Basic SQL JOIN types
SQL Server supports many kinds of different joins including INNER JOIN, SELF
JOIN, CROSS JOIN, and OUTER JOIN. In fact, each join type defines the way two tables are
related in a query. OUTER JOINS can further be divided into LEFT OUTER JOINS, RIGHT
OUTER JOINS, and FULL OUTER JOINS.
SQL INNER JOIN creates a result table by combining rows that have matching values in
two or more tables.
SQL LEFT OUTER JOIN includes in a result table unmatched rows from the table that is
specified before the LEFT OUTER JOIN clause.
SQL RIGHT OUTER JOIN creates a result table and includes into it all the records from
the right table and only matching rows from the left table.
SQL SELF JOIN joins the table to itself and allows comparing rows within the same table.
SQL CROSS JOIN creates a result table containing paired combination of each row of the
first table with each row of the second table.
dbForge SQL Complete
Enjoy even the most complex JOINs with SQL Complete
DOWNLOAD
INNER JOIN
INNER JOIN statement returns only those records or rows that have matching values and is
used to retrieve data that appears in both tables.
In our example, we want to extract data from the [Link] and [Link]
tables that are aliased with SOD for [Link] and P for [Link]. In the
JOIN statement, we match records in those columns. Make notice, how code suggestions work in
SQL Complete.
OUTER JOIN
When applying an SQL INNER JOIN, the output returns only matching rows from the stated
tables. In contrast, if you use an SQL OUTER JOIN, it will retrieve not only the matching rows
but also the unmatched rows as well.
The FULL OUTER JOIN returns a result that includes rows from both left and right tables. In
case, no matching rows exist for the row in the left table, the columns of the right table will have
nulls. Correspondingly, the column of the left table will have nulls if there are no matching rows
for the row in the right table.
LEFT OUTER JOIN
The LEFT OUTER JOIN gives the output of the matching rows between both tables. In case,
no records match from the left table, it shows those records with null values.
In our example, we want to join the tables [Link] and [Link] to
retrieve a list of all Person LastNames, but also show JobTitle if the Person is an Employee.
In the output, in case, there are no employees matching BusinessEntityID, NULL values will be
listed in the corresponding rows for NationalIDNumber and JobTitle.
RIGHT OUTER JOIN
The RIGHT OUTER JOIN works by the same principle as the LEFT OUTER JOIN.
The RIGHT OUTER JOIN selects data from the right table (Table B) and matches this data
with the rows from the left table (Table A). The RIGHT JOIN returns a result set that includes all
rows in the right table, whether or not they have matching rows from the left table. In case, a row
in the right table does not have any matching rows in the left table, the column of the left table in
the result set will have nulls.
SELF JOIN
The SELF JOIN allows you to join a table to itself. This implies that each row of the table is
combined with itself and with every other row of the table. The SELF JOIN can be viewed as a
join of two copies of the same table. The table is not actually copied, but SQL performs the
command as though it were. This is accomplished by using table name aliases to give each
instance of the table a separate name. It is most useful for extracting hierarchical data or
comparing rows within the same table.
In our example, we want to retrieve a list of all the territories and the salespeople working in
them from the [Link] table.
CROSS JOIN
The CROSS JOIN command in SQL, also known as a cartesian join, returns all combinations of
rows from each table. Envision that you need to find all combinations of size and color. In that
case, a CROSS JOIN will be an asset. Note, that this join does not need any condition to join two
tables. In fact, CROSS JOIN joins every row from the first table with every row from the second
table and its result comprises all combinations of records in two tables.