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:
We add a record in the table sales each day we sale these products:
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:
“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:
More complicated queries:
Show product names for which the number of rows in sales is strictly greater than 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”.
Show for each product name the latest sale:
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”.
Show for each product, the product name and the number of days during which we sold the product more than 45 times:
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”.