SQL Joins examples
Sun, 11/22/2009 - 13:05 — ndj
+-------------+---------------+--------------+
Table Setup
For clarity and ease of explanation and understanding, we’ll simplify the above situation just a bit.
Our company needs two tables: one, called customers, storing customer information, while the other one, called products is filled with our magnificent limited edition pens details (yeah, we sell unique pens…).
The resulting table layout of the query
CREATE TABLE `customers` ( `customer_id` INT( 11 ) NOT NULL , `customer_name` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `customer_id` ) ) ENGINE = MYISAM; <br /> CREATE TABLE `products` ( `pen_color ` VARCHAR( 255 ) NOT NULL , `customer_id` INT( 11 ) , PRIMARY KEY ( `pen_color ` ) ) ENGINE = MYISAM
will be something like:
+---------------+--------------+------+-----+---------+-------+ | FIELD | Type | NULL | KEY | DEFAULT | Extra | +---------------+--------------+------+-----+---------+-------+ | customer_id | int(11) | NO | PRI | NULL | | | customer_name | varchar(255) | NO | | NULL | | +---------------+--------------+------+-----+---------+-------+ <br /> +--------------+--------------+------+-----+---------+-------+ | FIELD | Type | NULL | KEY | DEFAULT | Extra | +--------------+--------------+------+-----+---------+-------+ | pen_color | varchar(255) | NO | PRI | | | | customer_id | int(11) | NO | | NULL | | +--------------+--------------+------+-----+---------+-------+
It’s important to notice how both tables have a customer_id column that can tie them together. This field represents the customer details (customers table) or what customer has ordered that particular pen (products table).
Your first simple JOIN
Alright, we got a lot of orders from our fellow aficionados and we want to start putting things together and ship the first few packages.
We have the following tables:
+-------------+---------------+ | customer_id | customer_name | +-------------+---------------+ | 1 | John | | 2 | Tim | | 3 | David | | 4 | Aaron | +-------------+---------------+ <br /> +--------------+-------------+ | pen_color | customer_id | +--------------+-------------+ | red | 1 | | blue | 3 | | yellow | 4 | | purple | 1 | | black | 0 | | green | 1 | | white | 3 | | brown | 0 | +--------------+-------------+
Hm, looks like someone wanted to test a few of our goodies!
So, to find out what colors John (ID #1), our longest-time customer has chosen this week, we’d run the following code:
SELECT * FROM customers JOIN products ON customers.customer_id = products.customer_id WHERE customers.customer_id = 1;
And here we go!
+-------------+---------------+--------------+-------------+ | customer_id | customer_name | pen_color | customer_id | +-------------+---------------+--------------+-------------+ | 1 | John | red | 1 | | 1 | John | purple | 1 | | 1 | John | green | 1 | +-------------+---------------+--------------+-------------+
On to LEFT JOIN
What the hell is a left join, you may be wondering.
Well, as the name very clearly states, it gives extra attention to the left table (being the first mentioned, on the left of the LEFT JOIN statement, customers in our example).
Each item of the left table will show up in the result, even thought there is no match with the right table.
Referring to the previous setup, the query
SELECT * FROM customers LEFT JOIN products ON customers.customer_id = products.customer_id;
yields
+-------------+---------------+--------------+-------------+ | customer_id | customer_name | pen_color | customer_id | +-------------+---------------+--------------+-------------+ | 1 | John | red | 1 | | 1 | John | purple | 1 | | 1 | John | green | 1 | | 2 | Tim | NULL | NULL | | 3 | David | blue | 3 | | 3 | David | white | 3 | | 4 | Aaron | yellow | 4 | +-------------+---------------+--------------+-------------+
The result shows the query preserved every customers, including those without any product ordered.
RIGHT JOIN is just the same
This time, though, the dominating table is the right one, so we expect to see all the products, regardless of their order status.
SELECT * FROM customers RIGHT JOIN products ON customers.customer_id = products.customer_id;
results in
+-------------+---------------+--------------+-------------+ | customer_id | customer_name | pen_color | customer_id | +-------------+---------------+--------------+-------------+ | 1 | John | red | 1 | | 3 | David | blue | 3 | | 4 | Aaron | yellow | 4 | | 1 | John | purple | 1 | | NULL | NULL | black | 0 | | 1 | John | green | 1 | | 3 | David | white | 3 | | NULL | NULL | brown | 0 | +-------------+---------------+--------------+-------------+
Selective RIGHT or LEFT JOIN
You can get a particular set of results specifing the WHERE clause after the JOIN statement.
SELECT * FROM customers RIGHT JOIN products ON customers.customer_id = products.customer_id WHERE products.customer_id = 0;
You’ll then select from the set you’d get from a regular RIGHT JOIN query all of the records that have not been bought yet.
+-------------+---------------+--------------+-------------+ | customer_id | customer_name | pen_color | customer_id | | NULL | NULL | black | 0 | | NULL | NULL | brown | 0 | +-------------+---------------+--------------+-------------+
FULL JOIN: get ‘em all!
Produces a of all records in the two tables, with matching records from both sides where available. If there is no match, though, the missing side will contain null.
SELECT * FROM customers FULL JOIN products ON customers.customer_id = products.customer_id;
results in…
+-------------+---------------+--------------+-------------+ | customer_id | customer_name | pen_color | customer_id | +-------------+---------------+--------------+-------------+ | 1 | John | red | 1 | | 3 | David | blue | 3 | | 4 | Aaron | yellow | 4 | | 1 | John | purple | 1 | | NULL | NULL | black | 0 | | 1 | John | green | 1 | | 3 | David | white | 3 | | NULL | NULL | brown | 0 | | 2 | Tim | NULL | NULL | +-------------+---------------+--------------+-------------+
INNER, OUTER and friends
You might have heard something about INNER and OUTER JOINS, but never understood what they really are used for.
By surprise, an INNER JOIN does a full join, thus acts in the same exact way as our first example does.
On the other side, the keyword OUTER is used when dealing with two or more tables only and may be added after the word LEFT or RIGHT or FULL in the last examples.
The reason of this all? ODBC compatibility!
CROSS JOIN
There’s also a so-called cartesian product available for SQL users.
This particular quesry joins everything to everuthing, resulting in a huge set of results that, sometimes can harm the system.
SELECT * FROM customers CROSS JOIN products;
The would result in 4 x 8 = 32 rows.
It should be pretty strightforward what would happen running this join against very large tables.
Self JOIN instead of a subquery
A Self join is basically a join on the same table.
Take the following example, and assume our customers table now looks like
+------------------+--------------+------+-----+---------+-------+ | FIELD | Type | NULL | KEY | DEFAULT | Extra | +------------------+--------------+------+-----+---------+-------+ | customer_id | int(11) | NO | PRI | NULL | | | customer_name | varchar(255) | NO | | NULL | | | customer_country | varchar(255) | NO | | NULL | | +------------------+--------------+------+-----+---------+-------+ <br /> +-------------+---------------+------------------+ | customer_id | customer_name | customer_country | +-------------+---------------+------------------+ | 1 | John | USA | | 2 | Tim | Germany | | 3 | David | Italy | | 4 | Aaron | USA | +-------------+---------------+------------------+
Now, suppose we found out John has’t paid his order and all the people from his country have learned how to trick our payment system. We want to find out who they are. But first, we must check which country John is from.
SELECT * FROM customers WHERE customer_country = (SELECT customer_country FROM customers WHERE customer_id = 1);
With the use of a subquery, the result is
+-------------+---------------+------------------+ | customer_id | customer_name | customer_country | +-------------+---------------+------------------+ | 1 | John | USA | | 4 | Aaron | USA | +-------------+---------------+------------------+
which is the same of (using aliases).
SELECT customers_1.* FROM customers AS customers_1 JOIN customers AS customers_2 ON customers_1.customer_country = customers_2.customer_country AND customers_2.customer_id = 1;
Joining and grouping
Have you ever wondered who is the customer making us always out of stock for certain colors?
A nice way to find out how many products a customer has ordered is by means of GROUP BY.
Here’s how to do it, instead of fetching all the customers and then, after each row, query again to count the products.
SELECT customers.customer_id, customers.customer_name, COUNT(products.pen_color) AS total_orders FROM customers JOIN products ON customers.customer_id = products.customer_id GROUP BY customers.customer_id;
And, to see people who haven’t ordered anything yet, use or beloved LEFT JOIN!
SELECT customers.customer_id, customers.customer_name, COUNT(products.pen_color) AS total_orders FROM customers LEFT JOIN products ON customers.customer_id = products.customer_id GROUP BY customers.customer_id;
+-------------+---------------+--------------+ | customer_id | customer_name | total_orders | +-------------+---------------+--------------+ | 1 | John | 3 | | 2 | Tim | 0 | | 3 | David | 2 | | 4 | Aaron | 1 |