Different types of JOINS in SQL and MYSQL
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.
- INNER JOIN (JOIN)
- LEFT OUTER JOIN or LEFT JOIN
- RIGHT OUTER JOIN or RIGHT JOIN
- FULL OUTER JOIN or FULL JOIN
- CROSS JOIN
- SELF JOIN
- NATURAL 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.
In others words - LEFT JOIN: Select records from the first (left-most) table with matching records of right table.
In others words - RIGHT JOIN: Select records from the second (right-most) table with matching records of left table.
In other words- FULL (OUTER) JOIN: Selects all records that match either left or right table records.
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;
A self join is simply when you join a table with itself.
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.
- Equi JOIN
- Theta JOIN | NonEqui Join
As the name itself indicates, it combines common records from two tables based on equality condition.
It is reverse of Equi-join where joining condition is uses other than equal operator(=) e.g, !=, <=, >=, >, < or BETWEEN etc