SQL JOIN Types
SQL is a language used for communicating and manipulating databases. A key aspect of SQL is its capability to combine data from multiple tables using various SQL JOIN types. Today, we are going learn about different types of JOINS in SQL, including:
- INNER JOIN (also called JOIN)
- LEFT JOIN (LEFT OUTER JOIN)
- RIGHT JOIN (RIGHT OUTER JOIN)
- FULL JOIN (FULL OUTER JOIN)
- SELF JOIN
- CROSS JOIN
- SQL JOINs (INNER, LEFT, RIGHT, FULL, SELF, and CROSS) are crucial for combining data from multiple tables in a database.
- INNER JOIN returns records with matching values in both joined tables, LEFT JOIN returns all records from the left (1st) table and matching records from the right table, and RIGHT JOIN returns all records from the right (2nd) table and matching records from the left table.
- FULL JOIN returns all records with matches from either table and SELF JOIN is used for joining a table with itself.
- Understanding when and how to use each JOIN type is essential for efficient data retrieval and analysis in SQL.
- Subqueries can be an alternative to JOINs but may be slower for large datasets while JOINs are often faster and more efficient.
INNER JOIN (JOIN)
The INNER JOIN, commonly referred to as a JOIN, is one of the most frequently used types of JOINS in SQL. This SQL JOIN type returns records that have matching values in both tables being joined.
Let's demonstrate this with a database of a bookstore which has two tables:
1. "Books" table:
2. "Authors" table:
We want to retrieve all books and their corresponding authors. The INNER JOIN query for this will look like this:
SELECT Books.Title, Authors.Author_Name FROM Books INNER JOIN Authors ON Books.Author_ID = Authors.Author_ID;
As you can see, the INNER JOIN only returns rows where there is a match on "Author_ID" in both the "Books" and "Authors" tables.
LEFT JOIN (LEFT OUTER JOIN)
The LEFT JOIN, also known as LEFT OUTER JOIN, is one of the SQL JOIN types commonly used in SQL. It returns all the records from left table (first table) and the matched records from the right table (second table). If there is no match, then result is NULL from the right side.
In LEFT OUTER JOIN, the left table is the one mentioned before the "LEFT JOIN OR LEFT OUTER JOIN" keyword, and the right table is the one mentioned after it. It's essential to identify the left and right tables correctly to obtain the desired results.
Let's continue with the same bookstore database and assume that we want to retrieve a list of all authors and any books they have written. We can achieve this using the LEFT JOIN.
SELECT Authors.Author_Name, Books.Title FROM Authors LEFT JOIN Books ON Authors.Author_ID = Books.Author_ID;
Here, all authors are listed along with their books. If an author hasn't written any book (i.e., they don't have any associated record in the "Books" table), the "Title" will be NULL.
RIGHT JOIN (RIGHT OUTER JOIN)
The RIGHT JOIN, also known as the RIGHT OUTER JOIN, is another SQL JOIN type. It is the exact opposite of the LEFT JOIN. It returns all the rows/records from the right table (2nd table which is mentioned after the JOIN statement) and the matched records from the left table (1st table which is mentioned before the JOIN statement). If there is no match, then result is NULL from the left side.
An important point to note is that RIGHT and LEFT JOINs are essentially the same thing with a different ordering of tables. Some developers prefer to use only LEFT JOINs for consistency and to avoid confusion.
Suppose we want to list all books and their respective authors. If a book doesn't have an associated author, we still want it listed. Here is the RIGHT JOIN query for this scenario:
SELECT Books.Title, Authors.Author_Name FROM Authors RIGHT JOIN Books ON Books.Author_ID = Authors.Author_ID;
The output shows all books and their authors. If a book doesn't have an associated author, "Author_Name" is displayed as NULL.
FULL JOIN (FULL OUTER JOIN)
The FULL JOIN, or FULL OUTER JOIN, is another SQL JOIN type. It returns all rows/records when there is a match in either the right or the left table records. When there is no match, the result is NULL on both sides.
In our bookstore example, suppose we want to list all books and all authors, regardless of whether a book has an associated author or an author has written a book. Here is the FULL JOIN SQL query for this scenario:
SELECT Books.Title, Authors.Author_Name FROM Books FULL JOIN Authors ON Books.Author_ID = Authors.Author_ID;
The output includes all books and all authors. Books without associated authors, or authors who haven't written any books, have NULL in the corresponding fields.
A SELF JOIN is one of the SQL JOIN types where a table is joined with itself. SQL does not have a specific keyword for self JOINS. Instead, it is achieved by using other SQL JOIN types, by using sub queries or by using just simple queries with Where clause. Self JOINS are useful for comparing rows within a table or when the data is related in a way that requires a JOIN to itself.
Let's take a new example with an "Employees" table, and look at different ways to do SELF JOIN:
If we want to get all employees whose manager is the same without explicitly using SQL JOIN types to write SELF JOIN query:
SELECT E1.Employee_Name as Employee, E1.Manager_ID as ManagerID FROM Employees E1, Employees E2 WHERE E1.Employee_ID <> E2.Employee_ID AND E1.Manager_ID = E2.Manager_ID
We can get same result by explicitly using SQL JOIN type.
SELECT e1.Employee_Name as Employee, e1.Manager_ID as ManagerID FROM Employees e1 JOIN Employees e2 ON e1.Manager_ID = e2.Manager_ID WHERE e2.Employee_ID <> e1.Employee_ID;
Although both queries return the same results, the second query is generally considered easier to read and understand due to the explicit JOIN keyword and ON clause, making it clearer what the join condition is. Also the newer JOIN syntax (the second query) is more universally accepted and is considered to be a best practice when writing SQL.
A CROSS JOIN in SQL returns the Cartesian product of rows from the tables. In other words, it will produce rows that combine each row from the 1st table with each row from the 2nd table. It is used to retrieve all combinations of records in two tables.
Using the same "Books" and "Authors" tables from the previous examples, if we want to create a combination of every book with every author (regardless of who wrote what), we would use the CROSS JOIN:
SELECT Books.Title, Authors.Author_Name FROM Books CROSS JOIN Authors;
This demonstrates all the different types of JOINS in SQL. Each of these SQL JOIN types has specific use cases and understanding how and when to use them is crucial to managing and manipulating data in SQL. Different contexts will call for different types of JOINS, and knowing which to use can greatly increase efficiency and accuracy in data retrieval and analysis.
Subquery vs JOIN
A subquery is a query embedded within another SQL query. While JOINS are used to retrieve data from multiple tables, subqueries can be used for the same purpose. So, when should you use a Subquery vs JOIN?
In general, subqueries can be slower than JOINS, especially for large datasets. JOINS are often faster and more efficient because they retrieve data from multiple tables in a single operation. It's considerable to note that this is not always the case. The performance can depend on various factors and sometimes subqueries can be faster.
However, subqueries can be more readable and easier to understand, especially for complex operations. It's often a matter of personal choice and the requirements of the task at hand.
Here's an example of a subquery achieving the same result as in the RIGHT JOIN example. We'll continue with our "Books" and "Authors" tables to list all books and their authors:
SELECT Books.Title, (SELECT Authors.Author_Name FROM Authors WHERE Books.Author_ID = Authors.Author_ID) as Author_Name FROM Books;
Here, for each row in the "Books" table, the subquery fetches the corresponding author's name from the "Authors" table.
Final Words for SQL JOIN Types
Understanding SQL JOIN types is fundamental to data manipulation and retrieval in SQL. This guide has provided us with a solid grounding on the different types of JOINS in SQL, complete with practical examples and the output to expect. It's very important to understand when and where to use each type of JOIN, whether you're optimizing for speed, efficiency, or readability.
Every JOIN type - INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN, SELF JOIN, and CROSS JOIN - has specific use cases and you're likely to encounter each in your SQL journey. Therefore, knowing these types of SQL JOINS and their respective applications is a necessity in becoming proficient in SQL.