Using Five Types of JOINS in MySQL
Using
Five Types of JOINS in MySQL
An
important component of any relational database system is the relationship
between tables. This relationship enables one to tie data in one table to data
in another table through SQL joins. The relationship is particularly useful
when you want to query related data from more than one table and retrieve a
meaningful result. Sometimes, this relationship may not be obvious but through
SQL they can be combined in a variety of ways and conclusions can be drawn based
upon conditions and clauses you provide. The article gives a quick introduction
to joins and their types using SQL in the MySQL database.
MySQL JOIN Overview
The
ability to query on the database that includes more than one table is referred
to as joining of tables. A JOIN therefore is an operation that matches rows
from one table to the rows in another. The matching is done in such a manner
that the columns from both the tables placed side by side although they may
have come from separate tables. There can be n-number of tables joined but
practically, a fewer number of tables generally result in a more efficient and
manageable query.
Let’s
create tables: CUSTOMERS, ORDERS and EMPLOYEES as follows:
CUSTOMERS
(cust_id [PK], cust_fname, cust_lname,
contact person, phone, address, city, state, pin code, country, sales_representive,
credit limit);
ORDERS (order_id [PK], order_date, reqd_date,
ship_date, status, other_info, cust_id
[FK]);
EMPLOYEES
(emp_id [PK], emp_fname, emp_lname,
email, job title);
You can join the table using an SQL query to obtain the list
of customers who have placed one or more orders with the list of dates the
order has been placed:
SELECT customers.cust_fname, orders.order_id,
orders.order_date
FROM customers
JOIN orders on customer. cust_id = orders.cust_id;
Note that, a join is specified by an SQL SELECT statement
with conditions in the FROM clause. The join operations can vary in form based
upon the JOIN clauses you apply. This directly effects the output produces by
the query. For example, you can retrieve query results by including only those
rows that match or unmatch the rows from left, right or both the tables. This
idea of using left, right and both the tables determines the structure of the
SQL query and the type of join used. They can be termed as -inner join,
left-outer join, right-outer join, full-outer join.
A quick introduction to five of them: inner join, outer join,
cross join, union, intersection are as follows:
1.
Inner join in MySQL
This is the simplest type of join where each row in one table
is matched with all other rows in another table. If the join condition
evaluates to true a row is returned otherwise it simply ignores the row. This
means that rows from customers and orders are
combined and the combination of row is returned only when the join condition
evaluates to TRUE as you can see in the query below. The same logic applies if
more than two tables are joined.
SELECT customers. cust_fname, orders.order_id,
orders.order_date
FROM customers INNER JOIN orders ON customer.cust_id =
orders.cust_id;
Result:
Order ID |
Customer Name |
Order Date |
10248 |
Vijay |
1996-07-04 |
10249 |
Vivek |
1996-07-05 |
10250 |
Sai |
1996-07-08 |
10251 |
Bharath |
1996-07-08 |
10252 |
Naveen |
1996-07-09 |
Interestingly, you can also write an inner join SQL query using
WHERE clause to have the same effect as follows.
SELECT customers.cust_fname, orders.order_id,
orders.order_date
FROM customers, orders
WHERE customers.cust_id = orders.cust_id;
In this case the join is done implicitly by the database optimizers.
This type of query though functionally equivalent is highly discouraged due to
its misleading nature as a simple SELECT query.
The INNER JOIN is the default join operation in MySQL
databases; therefore, the keyword INNER is optional.
2.
Outer join in MySQL
This is similar to INNER JOIN, but here you retrieve all the
rows from the left, right or both of the tables regardless of matching rows in
another table. Therefore, there are three types of outer joins:
A. Left-outer join in MySQL
This join returns all matched rows from the right table and all matched as well as unmatched rows from the left table. The left and right are signified by the table mentioned at the left and right of the JOIN keyword. However, NULL values are returned for the columns in the right table for the rows that do not match the JOIN condition.
SELECT customers.cust_fname, orders.order_id,
orders.order_date
FROM customers LEFT OUTER JOIN orders
ON customer.cust_id = orders.cust_id;
B. Right-outer join in MySQL
This join returns all matched rows from the left table and
all matched as well as unmatched rows from the right table. The left and right
are signified by the table mentioned at the left and right of the JOIN keyword.
However, NULL values are returned for the columns in the left table for the
rows that do not match the JOIN condition.
SELECT customers.cust_fname, orders.order_id,
orders.order_date
FROM customers RIGHT OUTER JOIN orders
ON customer.cust_id = orders.cust_id;
C. Full-outer join in MySQL
This join returns all matched and unmatched rows from both
the table concatenated together. For the columns from the unmatching join
condition, NULL values are returned.
Note that the full-outer join is not supported by MySQL
although you can emulate one by combining left and right-outer join with UNION
set operation. Oracle and SQL Server do support the full-outer join.
3.
Cross product in MySQL
A cross product between two table returns each row of the
left table concatenated with every row in the right table. Therefore, if there
are total M rows in Table A and there are N rows in Table B, a cross product
will consist of M x N rows. There is no direct support to create cross product
between tables in MySQL but you can use SELECT statement as follows to create
one.
SELECT customers.cust_fname, orders.order_id,
orders.order_date
FROM customers, orders;
Since, there are no join conditions all rows from customers
table is matched with the rows from the orders table. This simple statement
creates cross-product between two tables. Same logic applies if cross-join with
more than two table.
4.
Union in MySQL
This is the same as the set union operation. It joins all the rows in both tables without the duplicate rows. With the UNION operator you can combine the results of multiple SELECT queries into a single result. This essentially combines rows from one query with rows from another. The significant difference of UNION with typical JOIN operation is that join add columns obtained from multiple tables side by side whereas UNION adds rows to the end of the result set. It should be obvious that in order to apply UNION operation, there must be a compatible datatype and a number of columns produced by the SELECT statement.
SELECT customers.cust_fname, customers.cust_lname
FROM customers
UNION
SELECT employees.emp_fname, employees.emp_lname
FROM employees
5.
Intersection in MySQL
It is same as the set intersection operation. Similar to
UNION operator, it compares the out of two or more SELECT queries and return
the rows common to both the queries. MySQL has no support for INTERSECTION
operation. But you can emulate one as follows:
SELECT DISTINCT customers.cust_id
FROM customers
WHERE customers.cust_id IN (SELECT cust_id FROM orders);
or you can use INNER JOIN as follows to obtain the same
result:
SELECT DISTINCT customers.cust_id
FROM customers INNER JOIN orders USING (cust_id);
Conclusion
According to the combination how JOINs of tables are linked,
they can be represented as a left-deep, right-deep or bushy tree. This defines
the order of execution and intermediated joins. Typically, JOINs are used to
establish master-detail relationship where one base table is used to create a
relationship with one or more other sub-tables.
This is just a glimpse of the JOIN operations performed with SQL in MySQL database. A quick grasp on these would help one to dive deep on the topic.
Comments
Post a Comment