Joins in mysql


One of the great characteristics of SQL (Structured Query Language) is the fact that one can develop access and modify data across various tables. There are several benefits to this, including greater ease of manipulation, increased speed of access, and reduced data redundancy. In MYSQL (as well as many other SQL languages), this is accomplished via the join command. In short, a join command unites some or all of the data from two or more tables into one comprehensive structure. A JOIN is a means for combining fields from two tables by using values common to each.
There are two tables “Persons” and “Orders”.

The “Persons” table:

P_Id LastName FirstName Address City
1 Hansen Ola Timoteivn 10 Sandnes
2 Svendson Tove Borgvn 23 Sandnes
3 Pettersen Kari Storgt 20 Stavanger

 

The “Orders” table:

O_Id OrderNo P_Id
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 15

 

Types of JOIN:

INNER JOIN
An INNER JOIN is the most common join operation used in applications and can be regarded as the default join-type. Inner join creates a new result table by combing column values of two tables (A and B) based upon the join-predicate. The query compares each row of A with each row of B to find all pairs of rows which satisfy the join-predicate. When the join-predicate is satisfied, column values for each matched pair of rows of A and B are combined into a result row. The result of the join can be defined as the outcome of first taking the Cartesian product (or Cross Join) of all records in the tables (combining every record in table A with every record in table B)—then return all records which satisfy the join predicate. Actual SQL implementations normally use other approaches like a hash join or a sort-merge join where possible, since computing the Cartesian product is very inefficient.

 

SQL INNER JOIN Syntax

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons INNER JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

 

The result-set will look like this:

INNER JOIN:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678

 

 LEFT JOIN
The LEFT JOIN keyword returns all rows from the left table (Persons), even if there are no matches in the right table (Orders). In the other words, left join extract all data from left table and matched data from right table having null against unmatched records.

SQL LEFT JOIN Syntax

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

 

The result-set will look like this:

LEFT JOIN:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove NULL

 

 RIGHT JOIN

A right outer join (or right join) closely resembles a left outer join, except with the treatment of the tables reversed. Every row from the “right” table (B) will appear in the joined table at least once. If no matching row from the “left” table (A) exists, NULL will appear in columns from A for those records that have no match in B. A right outer join returns all the values from the right table and matched values from the left table (NULL in case of no matching join predicate).
SQL RIGHT JOIN Syntax:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons RIGHT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

 

The result-set will look like this:

RIGHT JOIN:

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
NULL NULL 34764

 

 EQUI JOIN

An equi-join (also known as an equijoin) is a specific type of comparator-based join, or theta join that uses only equality comparisons in the join-predicate. Using other comparison operators (such as SQL EQUI JOIN Syntax:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons, Orders WHERE Persons.P_Id=Orders.P_Id

 

The result-set will look like this:

EQUI JOIN:
LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678

 

 CROSS JOIN

The SQL CROSS JOIN produces a result set which is the number of rows in the first table multiplied by the number of rows in the second table, if no WHERE clause is used along with CROSS JOIN. This kind of result is called as Cartesian Product.

If, WHERE clause is used with CROSS JOIN, it functions like an INNER JOIN.

An alternative way of achieving the same result is to use column names separated by commas after SELECT and mentioning the table names involved, after a FROM clause.

SQL CROSS JOIN Syntax:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Orders CROSS JOIN Persons

 

The result-set will look like this:

CROSS JOIN:
LastName FirstName OrderNo
Hansen Ola 34764
Svendson Tove 77895
Pettersen Kari 77895
Hansen Ola 44678
Svendson Tove 44678
Pettersen Kari 44678
Hansen Ola 22456
Svendson Tove 22456
Pettersen Kari 22456
Hansen Ola 24562
Svendson Tove 24562
Pettersen Kari 24562
Hansen Ola 34764
Svendson Tove 34764
Pettersen Kari 34764

 

NATURAL JOIN

A NATURAL JOIN is a type of equi-join where the join predicate arises implicitly by columns in both tables that have the same column-names in the jointed tables. The resulting joined table contains only one column for each pair of equally named columns.

SQL NATURAL JOIN Syntax:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Orders NATURAL JOIN Persons

The result-set will look like this:

NATURAL JOIN

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678

 

 FULL JOIN

Mysql FULL JOIN is used to return all the records from both left and right outer join. The joined tables contain all records from both tables and fill null values for those missing matches on either side. The full join in MYSQL is the outcome result set of left outer join and right outer join using UNION clause.

SQL FULL JOIN Syntax:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons FULL JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

 

The result-set will look like this:

FULL JOIN

LastName FirstName OrderNo
Hansen Ola 22456
Hansen Ola 24562
Pettersen Kari 77895
Pettersen Kari 44678
Svendson Tove
34764

 

 SELF JOIN

A SELF JOIN is another type of join in sql which is used to join a table to itself, specially when the table has a FOREIGN KEY which references its own PRIMARY KEY.

 

SQL SELF JOIN Syntax:

SELECT A.LastName, A.FirstName, A.City FROM Persons A, Persons B WHERE A. City=B. City AND A. FirstName<>B. FirstName

 

The result-set will look like this:

SELF JOIN

LastName FirstName City
Svendson Tove Sandnes
Hansen Ola Sandnes

Leave a Reply