Types of joins in SQL

SQL join is an instruction to a database to combine data from more than one table. There are different kinds of joins, which have different rules for the results they create. In most SQL implementations you may find such joins as inner join, left join, right join, full join and also language implementation specific ones.

Different types of joins

  • INNER JOIN. An inner join produces a result set that is limited to the rows where there is a match in both tables for what we're looking for.

  • LEFT OUTER JOIN. A left outer join, or LEFT JOIN, results in a set where all of the rows from the first, or left hand side, table are preserved. The rows from the second, or right hand side table only show up if they have a match with the rows from the first table. Where there are values from the left table but not from the right, the table will read null, which means that the value has not been set.

  • RIGHT OUTER JOIN. A right outer join, or RIGHT JOIN, is the same as a left join, except the roles are reversed. All of the rows from the right hand side table show up in the result, but the rows from the table on the left are only there if they match the table on the right. Empty spaces are null, just like with the the left join.

  • FULL OUTER JOIN. A full outer join, or just OUTER JOIN, produces a result set with all of the rows of both tables, regardless of whether there are any matches. Similarly to the left and right joins, we call the empty spaces null.

  • CROSS JOIN. The cross join returns a table with a potentially very large number of rows. The row count of the result is equal to the number of rows in the first table times the number of rows in the second table. Each row is a combination of the rows of the first and second table.

  • SELF JOIN. You can join a single table to itself by means of inner join. In this case, you are using the same table twice.

SQL JOIN types

Source: dofactory.com

The SQL JOIN syntax example

General syntax is:

SELECT column-names
FROM table-name1 JOIN table-name2
ON column-name1 = column-name2
WHERE condition

where JOIN is one of the aforementioned join types.

Short answer

  • Join in SQL is an instruction to a database to combine data from more than one table.
  • There are different joins: inner join, left join, right join, full join, cross join.

Similar articles

Comments