Catalog / SQL Joins Cheatsheet
SQL Joins Cheatsheet
A quick reference guide to SQL joins, covering different types of joins, their usage, and examples. This cheatsheet provides a visual and practical understanding of how to combine data from multiple tables in SQL.
SQL Join Types
INNER JOIN
Returns rows when there is a match in both tables. Syntax:
Example:
|
The INNER JOIN keyword selects records that have matching values in both tables. |
If columns have the same names in multiple tables, use table names to specify the columns |
LEFT (OUTER) JOIN
Returns all rows from the left table, and the matched rows from the right table. If there is no match, the result is NULL on the right side. Syntax:
Example:
|
The LEFT JOIN keyword returns all records from the left table (Customers), even if there are no matches in the right table (Orders). |
RIGHT (OUTER) JOIN
Returns all rows from the right table, and the matched rows from the left table. If there is no match, the result is NULL on the left side. Syntax:
Example:
|
The RIGHT JOIN keyword returns all records from the right table (Customers), even if there are no matches in the left table (Orders). |
FULL (OUTER) JOIN
Returns all rows when there is a match in one of the tables. Combines the results of both LEFT and RIGHT outer joins. Syntax:
Example:
|
The FULL OUTER JOIN keyword returns all records from both tables: Customers and Orders. It fills NULL where there is no match |
SQL Join Conditions and Filtering
Using WHERE Clause with Joins
The Example:
This query selects order IDs and customer names only for customers from the USA. |
Use aliases for better readability when using the |
JOIN with Multiple Conditions
Joins can include multiple conditions using Example:
This query selects employees’ first names and order IDs for orders shipped after January 1, 2023. |
Using Aliases in Joins
Aliases can be used to give tables a temporary name, making queries shorter and more readable. Syntax:
Example:
|
Use aliases, especially when joining tables with similar column names. |
Advanced SQL Join Techniques
Self Join
A self join is used to join a table to itself, as if the table were two different tables. Syntax:
Example:
This query finds the names of employees and their managers from the same |
Use self joins to compare values within the same table. |
Cross Join
A cross join returns the Cartesian product of rows from the tables. Each row from the first table is combined with each row from the second table. Syntax:
Example:
This query generates all possible combinations of customers and products. |
Avoid using cross joins on large tables without a |
Natural Join
A natural join is a join based on all columns having the same name and data type in both tables. The common columns must be specified with the same name in both tables. It implicitly joins rows based on matching values in all common columns. Syntax:
Example:
|
Practical SQL Join Examples
Joining Three Tables
SQL joins can be extended to combine data from more than two tables by chaining multiple Example:
This query combines customer names, order IDs, and shipper names from three different tables. |
Complex Join with Subqueries
Subqueries can be used within Example:
This query selects customer names and order IDs for orders placed after January 1, 2023, using a subquery. |
Using CASE Statements in Joins
The Example:
This query selects customer names, order IDs, and a shipping status determined by whether the |