Types Of Joins in SQL

SQL Joins facilitate merging data from multiple tables based on a piece of shared information, often called a “key.” This key acts as a connector between the tables. Without SQL Joins, we would need to execute multiple queries and combine their results programmatically, which could be inefficient and time-consuming. SQL Joins save us from these difficulties by enabling complex data retrieval in a single query.

Photo by Duy Pham on Unsplash

Several types of joins exist in SQL:

1. Inner Join
2. Left Outer Join (or Left Join)
3. Right Outer Join (or Right Join)
4. Full Outer Join (or Full Join)
5. Cross Join (or Cartesian Join)

This article explores the various types of joins in SQL and their applications. To enhance our discussion, use data from the following link. Run the code provided in your SQL Server Management Studio or any other SQL client that supports executing SQL queries to better understand the concepts discussed.

Inner Join

Presentation of inner join

Inner Join returns only the rows with a match in both tables based on the join condition.

SELECT 
    Q.Title AS QuestionTitle,
    A.Body AS AnswerBody
FROM 
    Questions Q
INNER JOIN 
    Answers A ON Q.QuestionId = A.QuestionId;

Inner join returns only the rows with a match in both tables. In the context of questions and answers, this query will return the titles of questions along with the corresponding answers. The result will not include rows where a question doesn’t have an answer or vice versa.

Results for inner Join

Left Outer Join (or Left Join)

Presentation of left join

Returns all rows from the left table, and the matched rows from the right table. If there is no match, NULL values are returned for the columns of the right table.

SELECT 
    Q.Title AS QuestionTitle,
    A.Body AS AnswerBody
FROM 
    Questions Q
LEFT JOIN 
    Answers A ON Q.QuestionId = A.QuestionId;

Retrieves all rows from the Question table and matching rows from the Answer table based on the QuestionId column. NULL values are returned for the columns from the Answers table where there is no matching row.

Result of left join

Right Outer Join (or Right Join)

Presentation of right join

Returns all rows from the right table, and the matched rows from the left table. If there is no match, NULL values are returned for the columns of the left table.

SELECT 
    Q.Title AS QuestionTitle,
    A.Body AS AnswerBody
FROM 
    Questions Q
RIGHT JOIN 
    Answers A ON Q.QuestionId = A.QuestionId;

Right join returns all rows from the right table (Answers), and the matched rows from the left table (Questions). If there is no match, the result will contain NULL values for the columns from the left table. This type of join is the reverse of the left join. In this case, all answers will be returned, and questions will be matched if available. Answers without questions will have NULL values in the QuestionTitle column.

Results of right join

Full Outer Join (or Full Join)

Presentation of full join

Returns all rows when there is a match in either table. If there is no match, NULL values are returned for the columns of the table that lacks a match.

SELECT 
    Q.Title AS QuestionTitle,
    A.Body AS AnswerBody
FROM 
    Questions Q
FULL OUTER JOIN 
    Answers A ON Q.QuestionId = A.QuestionId;

Full outer join returns all rows from both tables, matching them where possible and filling in NULLs for missing matches on either side. This type of join ensures that no data is lost from either table. In this context, all questions and answers will be returned. Rows with no match in the Questions or Answers table will have NULL values for the respective columns.

Results of full join

Cross Join (or Cartesian Join)

Returns the Cartesian product of the two tables, resulting in a combination of every row from the first table with every row from the second table. It doesn’t require a join condition.

SELECT 
    Q.Title AS QuestionTitle,
    A.Body AS AnswerBody
FROM 
    Questions Q
CROSS JOIN 
    Answers A;

This query will combine each question with every answer, resulting in a dataset where every question is matched with every answer. Cross joins are less commonly used compared to inner, left, and right joins, as they tend to produce a large result set that may not be meaningful in many scenarios.

Result of cross-join

Summary

SQL joins are like bridges connecting diverse datasets effortlessly and enabling seamless querying. They simplify data analysis by providing a unified view, enhancing the efficiency and effectiveness of database operations.

Exploring SQL joins, including INNER, LEFT, RIGHT, FULL OUTER, and CROSS JOIN, reveals how data can be combined and queried in database management.