In this blog post, we will discuss the different type of joins in SQL. The joins that are available in SQL are:

  • [INNER] JOIN - Returns only records that match the values in the tables that are being joined.
  • LEFT [OUTER] JOIN - All records from the left table are returned and the records that match in the right table are returned. Rows in the right table which do not have a match will also be returned, but will have null values.
  • RIGHT [OUTER] JOIN - All records from the right table are returned and the records that match in the left table are returned. Rows in the left table which do not have a match will also be returned, but will have null values.
  • FULL [OUTER] JOIN - All records are returned of the matched records in the left or right table. Rows that do not match will also be returned, but will have null values.

There are two additional types of joins:

  • SELF JOIN - This type of join is where the same table with itself. It’s query would be as follows:

        SELECT [COLUMN(S)] FROM TABLEA AS TBLA, TABLEA AS TBLB
    WHERE [CONSTRAINTS];

  • CROSS JOIN - This type of join outputs a set where the resultant number of rows in TableA, for example, is multipled by the number of rows in TableB if there is no where clause used. The result of this result is called Cartesian Product. If a WHERE clause is used, the cross join acts as a regular [INNER] JOIN.

Keywords that are contained within the [] are optional.