SQL joins are an essential part of database management. They allow you to combine data from two or more tables in a database, based on a common field or set of fields. This is useful when you need to retrieve data that is spread across multiple tables, and you want to see it all in one place.
There are several types of SQL joins, including inner join, left join, right join, and full outer join. Each type of join has its own specific use case, depending on the data you are working with and the results you want to achieve.
Inner join is the most common type of join, and it returns only the rows that have matching values in both tables. Left join returns all the rows from the left table and the matching rows from the right table. Right join is the opposite of left join, returning all the rows from the right table and the matching rows from the left table. Full outer join returns all the rows from both tables, whether or not they have matching values.
This cheat sheet provides an overview of SQL joins and their syntax.
Inner Join
An inner join returns only the rows that have matching values in both tables.
| Syntax | Description |
|---|---|
SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column; | Returns all columns from both tables where the values in the specified columns match. |
Left Join
A left join returns all the rows from the left table and the matching rows from the right table. If there is no match, NULL values are returned for the right table columns.
| Syntax | Description |
|---|---|
SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column; | Returns all columns from both tables where the values in the specified columns match. If there is no match in the right table, NULL values are returned for the right table columns. |
Right Join
A right join returns all the rows from the right table and the matching rows from the left table. If there is no match, NULL values are returned for the left table columns.
| Syntax | Description |
|---|---|
SELECT * FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; | Returns all columns from both tables where the values in the specified columns match. If there is no match in the left table, NULL values are returned for the left table columns. |
Full Outer Join
A full outer join returns all the rows from both tables and NULL values for the columns that do not have a match.
| Syntax | Description |
|---|---|
SELECT * FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column; | Returns all columns from both tables. If there is no match in either table, NULL values are returned for the columns that do not have a match. |
Cross Join
A cross join returns the Cartesian product of both tables, which means that every row from the left table is combined with every row from the right table.
| Syntax | Description |
|---|---|
SELECT * FROM table1 CROSS JOIN table2; | Returns all columns from both tables, with every row from the left table combined with every row from the right table. |
Self Join
A self join is a join where a table is joined with itself.
| Syntax | Description |
|---|---|
SELECT * FROM table1 t1 INNER JOIN table1 t2 ON t1.column = t2.column; | Returns all columns from the table, where the values in the specified columns match within the same table. |