Tutorial by Examples

A basic join (also called "inner join") queries data from two tables, with their relationship defined in a join clause. The following example will select employees' first names (FName) from the Employees table and the name of the department they work for (Name) from the Departments table:...

Joins can also be performed by having several tables in the from clause, separated with commas , and defining the relationship between them in the where clause. This technique is called an Implicit Join (since it doesn't actually contain a join clause). All RDBMSs support it, but the syntax is usua...

A Left Outer Join (also known as a Left Join or Outer Join) is a Join that ensures all rows from the left table are represented; if no matching row from the right table exists, its corresponding fields are NULL. The following example will select all departments and the first name of employees that ...

A table may be joined to itself, with different rows matching each other by some condition. In this use case, aliases must be used in order to distinguish the two occurrences of the table. In the below example, for each Employee in the example database Employees table, a record is returned containi...

Cross join does a Cartesian product of the two members, A Cartesian product means each row of one table is combined with each row of the second table in the join. For example, if TABLEA has 20 rows and TABLEB has 20 rows, the result would be 20*20 = 400 output rows. Using example database SELECT d...

Joining a subquery is often used when you want to get aggregate data from a child/details table and display that along with records from the parent/header table. For example, you might want to get a count of child records, an average of some numeric column in child records, or the top or bottom row ...

A very interesting type of JOIN is the LATERAL JOIN (new in PostgreSQL 9.3+), which is also known as CROSS APPLY/OUTER APPLY in SQL-Server & Oracle. The basic idea is that a table-valued function (or inline subquery) gets applied for every row you join. This makes it possible to, for example...

One type of JOIN that is less known, is the FULL JOIN. (Note: FULL JOIN is not supported by MySQL as per 2016) A FULL OUTER JOIN returns all rows from the left table, and all rows from the right table. If there are rows in the left table that do not have matches in the right table, or if there a...

Recursive joins are often used to obtain parent-child data. In SQL, they are implemented with recursive common table expressions, for example: WITH RECURSIVE MyDescendants AS ( SELECT Name FROM People WHERE Name = 'John Doe' UNION ALL SELECT People.Name FROM Peopl...

SQL has various join types to specify whether (non-)matching rows are included in the result: INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, and FULL OUTER JOIN (the INNER and OUTER keywords are optional). The figure below underlines the differences between these types of joins: the blue area repres...

Let's say we have two tables (A and B) and some of their rows match (relative to the given JOIN condition, whatever it may be in the particular case): We can use various join types to include or exclude matching or non-matching rows from either side, and correctly name the join by picking the cor...