Advanced Selection Techniques: Using WHERE and JOIN Clauses

Advanced selection in MySQL – more complex WHERE conditions, subqueries, and JOIN clauses for pulling data from multiple tables.

WHERE beyond the basics

You have already seen simple WHERE filters. Here are a couple of more advanced uses.

Conditional logic

AND, OR, and NOT let you build complex conditions. Products that are out of stock or cost more than £100:

SELECT * FROM products WHERE NOT in_stock OR price > 100;

Subqueries

A subquery in WHERE can filter based on the result of another query. Products more expensive than the average price:

SELECT * FROM products WHERE price > (SELECT AVG(price) FROM products);

MySQL calculates the average price first, then returns products above that figure.

JOIN clauses

JOINs combine data from two or more tables based on a related column. That is the core of relational database work.

INNER JOIN

INNER JOIN returns rows where there is a match in both tables. With a products table and an orders table:

SELECT products.name, orders.order_date 
FROM products 
INNER JOIN orders ON products.id = orders.product_id;

That fetches product names and order dates where the product IDs match.

LEFT JOIN and RIGHT JOIN

LEFT JOIN returns all rows from the left table plus matched rows from the right. Unmatched rows get NULLs for the right table’s columns. RIGHT JOIN is the opposite.

All products and any orders linked to them:

SELECT products.name, orders.order_date 
FROM products 
LEFT JOIN orders ON products.id = orders.product_id;

That includes products that have never been ordered.

CROSS JOIN

CROSS JOIN produces every possible combination of rows from both tables (a Cartesian product). It is rarely what you want, but it exists:

SELECT products.name, orders.order_date 
FROM products 
CROSS JOIN orders;

JOIN with WHERE

You can combine JOIN and WHERE for tighter results. All orders for products priced over £100:

SELECT orders.id, products.name 
FROM orders 
INNER JOIN products ON orders.product_id = products.id 
WHERE products.price > 100;

Only orders for products that meet the price condition are returned.

Tips and best practice

1. Use aliases: when tables share column names, aliases keep things readable.

SELECT p.name, o.order_date 
FROM products AS p 
INNER JOIN orders AS o ON p.id = o.product_id;

2. Index JOIN columns: indexed columns used in JOINs perform much better on large tables.
3. Be careful with CROSS JOIN: it can produce a huge number of rows very quickly.

Wrapping up

Subqueries and JOINs let you ask more precise questions of your data. Try combining WHERE and JOIN in different ways on your own tables – that is the fastest way to get comfortable with them.