JOINs

Inner join

The inner join selects rows that have matching values in both tables.

Syntax:

SELECT myTable.column1,myTable.column2,myOtherTable.column1,....
FROM mytable INNER JOIN table2
ON table1.matching_column = myOtherTable.matching_column;

Example of inner join

Joins a table containing user transactions with a table containing promotions shown to the users, to show the spending for every userID.

Left join

A left join returns all values from the left relation and the matched values from the right table, or appends NULL if there is no match. Also referred to as a left outer join.

Syntax:

Right join

A right join returns all values from the right relation and the matched values from the left relation, or appends NULL if there is no match. It is also referred to as a right outer join.

Syntax:

Full join

A full join returns all values from both relations, appending NULL values on the side that does not have a match. It is also referred to as a full outer join.

Syntax:

Cross join

Syntax:

Semi/Anti join

Semi/anti-join returns rows from the first table where no matches are found in the second table. Returns one copy of each row in the first table for which no match is found.

Syntax:

Some subqueries, like the following are also implemented as a semi-join under the hood:

Equi join

An equi join uses an equality operator to match a single or multiple column values of the relative tables.

Syntax: