One of the most powerful SQL features is the JOIN operation, providing an elegant and simple means of combining every row from one table with every row from another table. However, there are times that we may want to find values from one table that are NOT present in another table. As we'll see in today's blog article, joins can be utilized for this purpose as well, by including a predicate on which to join the tables. Known as anti joins, these can be helpful in answering a variety of business-related questions, such as:
- Which customers did not place an order?
- Which employees have not been assigned a department?
- Which salespeople did not close a deal this week?
This blog will offer a primer on the types of anti joins and how to write them using a few examples based on the PostgreSQL dvdrental database. We'll write and execute the queries in Navicat Premium Lite 17.
Most database developers and administrators are familiar with the standard inner, outer, left, and right JOIN types. While these can be written using ANSI SQL, there are other types of joins that are based on relational algebra operators that don't have a syntax representation in SQL. Today we'll be looking at one such join type: the Semi Join. Next week we'll tackle the similar Anti Join. To gain a better understanding of how these types of joins work, we'll execute some SELECT queries in Navicat Premium Lite 17 against the PostgreSQL dvdrental database. It's a free database that's based on the MySQL Sakila Sample Database.
If you have been writing SQL queries for some time, you are probably quite familiar with the WHERE clause. While it has no effect on aggregated fields, there is a way to filter records according to aggregate values, and that is by using the HAVING clause. This blog will cover how it works as well as provide a few examples on using it in SELECT queries.
The SQL EXISTS operator offers us an easy way to retrieve data based on the existence (or non-existence) of some other data. More specifically, it's a logical operator that evaluates the results of a subquery and returns a boolean value indicating whether rows were returned or not. While the IN operator can be utilized for much the same purpose, there are some differences to be aware of. Today's blog will cover how to use the EXISTS operator using a few examples as well as provide some guidance as to when to use EXISTS rather than IN.
In the mid nineties, Sun Microsystems came out with a language that you could "write once, [and] run everywhere." That language was, of course, Java. And, while it did go on to be one of the most popular programming languages until this day, their slogan turned out to be just a little optimistic. The course of the Java language does bear some strong similarities to that of SQL. It too can be ported from one database to another, or even across operating systems, with little or no modification. At least, that's the dream. In the real world, production-level code tends to require some tweaking in order to work in a new environment. This blog will outline some of the reasons that SQL syntax may differ across different database vendors.
- 2024 (1)
- 2023 (1)
- 2022 (1)
- 2021 (1)
- 2020 (1)
- 2019 (1)
- 2018 (1)
- 2017 (1)
