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;