Join Tables
Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Caution: Performance Considerations
DBMSs process joins at run-time relating each table as specified. This process can become very resource intensive so be careful not to join tables unnecessarily. The more tables you join the more performance will degrade.
Inner Join
# example: inner join two tables
SELECT vend_name, vend_id, prod_name, prod_price
FROM Vendors
INNER JOIN Products ON Vendors.vend_id = Products.vend_id;
# example: inner join multiple tables
SELECT cust_name, cust_contact
FROM Customers
INNER JOIN Orders ON Customers.cust_id = Orders.cust_id
INNER JOIN Orderitems ON Orders.order_num = OrderItems.order_num
WHERE prod_id = "RGAN01";
Left/Right Outer Join
An outer join is the join that includes table rows that have no associated rows in the related table.
There are always two basic forms of outer joins—the left outer join and the right outer join. The only difference between them is the order of the tables that they are relating. In other words, a left outer join can be turned into a right outer join simply by reversing the order of the tables in the FROM or WHERE clause. When using OUTER JOIN
syntax you must use the RIGHT
or LEFT
keywords to specify the table from which to include all rows.
SELECT Customers.cust_id, Orders.cust_id, order_num
FROM Customers
LEFT OUTER JOIN Orders ON Customers.cust_id = Orders.cust_id;
# the following syntax gives the same results
SELECT Customers.cust_id, Orders.cust_id, order_num
FROM Orders
RIGHT OUTER JOIN Customers ON Customers.cust_id = Orders.cust_id;
Full Outer Join
The FULL OUTER JOIN
keyword return all records when there is a match in either left (table1) or right (table2) table records.
Note: FULL OUTER JOIN
can potentially return very large result-sets!
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;
Note that MySQL doesn't support FULL OUTER JOIN
.
Self Join
A self join is a join of a table to itself. This table appears twice in the FROM clause and is followed by table aliases that qualify column names in the join condition.
Self-joins are often used to replace statements using subqueries that retrieve data from the same table as the outer statement.
Example: Suppose you wanted to send a mailing to all the customers whose names are the same as customers whose contact is "Jim Jones".
# use subqueries
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name IN (SELECT cust_name
FROM Customers
WHERE cust_contact = "Jim Jones");
# use self join
SELECT C1.cust_id, C1.cust_name, C1.cust_contact
FROM Customers AS C1, Customers AS C2
WHERE C1.cust_name = C2.cust_name
AND C2.cust_contact = "Jim Jones";
Remember that when use self join, we need to give aliases for the two same tables. When we retrieve any column in the table, we need to specify the aliases for the table.
Cross Join
Cross join returns the cartesian product of the two tables. There is no condition or clause as each row of the table1 is joined to each row the table2.
SELECT *
FROM table1
CROSS JOIN table2;
Natural Join
Whenever tables are joined, at least one column will appear in more than one table (the columns being used to create the join). Standard joins return all data, even multiple occurrences of the same column. A natural join simply eliminates those multiple occurrences so that only one of each column is returned.
How does it do this? The answer is it doesn’t—you do it. A natural join is a join in which you select only columns that are unique. This is typically done using a wildcard (SELECT *) for one table and explicit subsets of the columns for all other tables.
SELECT c.*, o.order_num, o.order_date
FROM Customers AS c, Orders AS o
WHERE c.cust_id = o.cust_id;
Equijoin:
Equijoin works the same as inner join. Per the ANSI SQL specification, use of the INNER JOIN syntax is preferred over the simple equijoins syntax used previously.
To create a equijoin, specify all the tables to be included in FROM
clause and how they are related to each other in WHERE
clause. When you join two tables, what you are actually doing is pairing every row in the first table with every row in the second table.
# Equijoin two tables
SELECT vend_name, vend_id, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
Caution: You must use the fully qualified column name (table name and column name separated by a period) whenever there is a possible ambiguity about which column you are referring to.
# Equijoin multiple tables
SELECT cust_name, cust_contact
FROM Customers, Orders, OrderItems
WHERE Customers.cust_id = Orders.cust_id
AND OrderItems.order_num = Orders.order_num;
Join with several conditions:
We can use various filter functions in ON
caluses to specify several joining conditions.
Here is a example for self joining temperature table with yesterday's data when yesterday's temperature is lower.
# using where for the second condition
SELECT w1.Id AS Id
FROM Weather AS w1
JOIN Weather AS w2 ON DATEDIFF(w1.Date, w2.Date) = 1
WHERE w1.Temperature > w2.Temperature;
# specify all conditions using ON clause
SELECT w1.Id AS Id
FROM Weather AS w1
JOIN Weather AS w2 ON DATEDIFF(w1.Date, w2.Date) = 1 AND w1.Temperature > w2.Temperature;
Last updated
Was this helpful?