Different types of JOINS in SQL and MYSQL

Home >> Blog >> Different types of JOINS in SQL and MYSQL
By Admin Sep 06, 2019
SQL JOIN - SQL Join Types - Inner Join, Outer Join, Left Join, Right Join. SQL Join Syntax with SQL Join Example. Learn SQL Joins in DBMS
What is SQL Join?
A SQL JOIN clause is used to retrieve data from two or more tables in database.

A JOIN works on one, two or more tables if they have at least one common field and have a relationship between them.

SQL Join Types - Types of JOINS in SQL
  1. INNER JOIN (JOIN)
  2. OUTER JOIN
    1. LEFT OUTER JOIN or LEFT JOIN
    2. RIGHT OUTER JOIN or RIGHT JOIN
    3. FULL OUTER JOIN or FULL JOIN
  3. CROSS JOIN
  4. SELF JOIN
  5. NATURAL JOIN
1. JOIN or INNER JOIN :

In this kind of a JOIN, we get all records that match the condition in both the tables

In other words - Inner join: Select records that have matching values in both tables.
Remarks:

The Inner Join refers to the intersection of two (or more) tables.

Join and Inner Join are functionally equivalent, but INNER JOIN can be a bit clear to read, especially if the query has other join types (i.e. LEFT or RIGHT or CROSS) included in it.

2. OUTER JOIN :
Outer Join retrieves Either, the matched rows from one table and all rows in the other table Or, all rows in all tables (it doesn't matter whether or not there is a match). There are three kinds of Outer Join :
2.1. LEFT OUTER JOIN or LEFT JOIN
This join returns all the rows from the left table in conjunction with the matching rows from the right table. If there are no columns matching in the right table, it returns NULL values.
In others words - LEFT JOIN: Select records from the first (left-most) table with matching records of right table.
2.2. RIGHT OUTER JOIN or RIGHT JOIN
This join returns all the rows from the right table in conjunction with the matching rows from the left table. If there are no columns matching in the left table, it returns NULL values.
In others words - RIGHT JOIN: Select records from the second (right-most) table with matching records of left table.
2.3. FULL OUTER JOIN or FULL JOIN
This join combines left join and right join. It returns rows from either table when the conditions are met and returns NULL value when there is no match.
In other words- FULL (OUTER) JOIN: Selects all records that match either left or right table records.
CROSS JOIN:

It is the Cartesian product of the two tables involved.

Its result is the number of records in the first table multiplied by the number of records in the second table, showing the columns altogether. 

SELECT * FROM department, employee

or

SELECT * FROM department CROSS JOIN employee;
SELF JOIN:

A self join is simply when you join a table with itself.

NATURAL JOIN:

It is based on the two conditions : the JOIN is made on all the columns with the same name for equality.

  • - Removes duplicate columns from the result.
  • - This seems to be more of theoretical in nature and as a result (probably) most DBMS don't even bother supporting this.
JOINs based on Operators
  1. Equi JOIN
  2. Theta JOIN | NonEqui Join
Equi JOIN:

As the name itself indicates, it combines common records from two tables based on equality condition.

Theta JOIN (Non-Equi Join):

It is reverse of Equi-join where joining condition is uses other than equal operator(=) e.g, !=, <=, >=, >, < or BETWEEN etc