SQL Samurai Training: 4 Joining Techniques

Add these SQL joining techniques to your arsenal:

  • simple join between two tables using aliases.
  • using a query in parenthesis in place of a column in a where clause.
  • using a query in parenthesis in place of a numeric value in a where clause.
  • using a query in parenthesis in place of a column to display.

Tables to join: products and sales.

We are going to join two tables: products and sales.

The table products has a list of products:

IDproduct_nameproduct_price
1white table1034
2red umbrella1009
3white armchair980

We add a record in the table sales each day we sale these products:

IDproduct_idquantitysales_date
111002014-10-05
21982014-10-06
32232014-10-04
43892014-10-03
53452014-10-05

product_id is the ID of the product sold. It corresponds to the ID stored in the products table. “quantity” is the number of times we sold such a product in the day equal to sales_date. Example: we sold 98 times on the 6th of October the product which ID is 1, and this product is a white table.

Simple Join: what we want to show.

We want to show the following information for each row in the table sales:

  • product name (from the products table).
  • price (from the products table).
  • quantity (from the sales table).
  • sales_date (from the sales table).

Simple Join: how to show such a result.

We are using the following syntax:

select
p.product_name,
p.product_price,
s.quantity,
s.sales_date
from products p,
sales s
where
p.id = s.product_id

“p” is an alias of products, and “s” an alias for sales. “p.product_name” corresponds to the column product_name in the table products (which alias is p). This query is joining the two tables by matching the id of the products table with the product_id of the sales table.

Here is the result of this query:

product_nameproduct_pricequantitysales_date
white table10341002014-10-05
white table1034982014-10-06
red umbrella1009232014-10-04
white armchair980892014-10-03
white armchair980452014-10-05

More complicated queries:

Show product names for which the number of rows in sales is strictly greater than 1:

select p.product_name
from products p
where (
select count(*)
from sales s
where
p.id = s.product_id
) > 1

Note that “select count(*) from sales s where p.id = s.product_id” is returning an integer (number of entries calculated by count(*)). This integer is then compared to “1”.

product_name
white table
white armchair

Show for each product name the latest sale:

select
p.product_name,
p.product_price,
s.quantity,
s.sales_date
from products p,
sales s
where
p.id = s.product_id
and s.sales_date =
(
select
max(sales_date)
from sales s2
where
s.product_id =
s2.product_id
)

Note that we joined the sales table with itself to find out what is the latest sales date for each product ID. In that case, we must use two different alias (in this example: “s” and “s2”). Also, in the where clause, the column sales_date is compared to the value given by the query in parenthesis: “select max(...”. The query in parenthesis must return only one field (one row, one column) for one value of “s.product_id”.

product_nameproduct_pricequantitysales_date
white table1034982014-10-06
red umbrella1009232014-10-04
white armchair980452014-10-05

Show for each product, the product name and the number of days during which we sold the product more than 45 times:

select
p.product_name,
(
select count(*)
from sales s
where
p.id = s.product_id
and s.quantity > 45
) as big_sales_count
from products p

Note that we put a query in parenthesis instead of a column name to display (between the keyword select and the keyword from. For this two work, the query has to return only one field (one row only, one column only) for each value of “p.id”.

product_namebig_sales_count
white table2
red umbrella0
white armchair1
 
Share on:



Author:
Last udpated: January 6, 2015
Created: October 9, 2014