SQL JOIN Types

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;

Output:

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.

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;

Output:

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.

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;

Output:

The output shows all books and their authors. If a book doesn't have an associated author, "Author_Name" is displayed as NULL.

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;

Output:

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:

"Employees" table:

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;

Output:

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;

Output:

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.

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;

Output:

Here, for each row in the "Books" table, the subquery fetches the corresponding author's name from the "Authors" table.