Notifications
Clear all
Inner Joins:
- JOIN orders
- ON customers.id = orders.customer_id;
- SELECT *
- FROM orders
- JOIN customers
- ON customers.id = orders.customer_id;
-- ARBITRARY JOIN - meaningless, but still possible
- SELECT * FROM customers
- JOIN orders ON customers.id = orders.id;
Posted : 06/07/2020 4:47 pm
Left Join:
- SELECT * FROM customers
- LEFT JOIN orders
- ON customers.id = orders.customer_id;
- SELECT first_name, last_name, order_date, amount
- FROM customers
- LEFT JOIN orders
- ON customers.id = orders.customer_id;
- SELECT
- first_name,
- last_name,
- IFNULL(SUM(amount), 0) AS total_spent
- FROM customers
- LEFT JOIN orders
- ON customers.id = orders.customer_id
- GROUP BY customers.id
- ORDER BY total_spent;
Posted : 06/07/2020 5:45 pm
Right Join:
- SELECT
- IFNULL(first_name,'MISSING') AS first,
- IFNULL(last_name,'USER') as last,
- order_date,
- amount,
- SUM(amount)
- FROM customers
- RIGHT JOIN orders
- ON customers.id = orders.customer_id
- GROUP BY first_name, last_name;
Posted : 07/07/2020 4:15 am
Posted : 07/07/2020 4:15 am
- (INNER) JOIN: Select records that have matching values in both tables.
- FULL (OUTER) JOIN: Selects all records that match either left or right table records.
- LEFT (OUTER) JOIN: Select records from the first (left-most) table with matching right table records.
- RIGHT (OUTER) JOIN: Select records from the second (right-most) table with matching left table records.
Note: All INNER and OUTER keywords are optional.
Details about the different JOINs are available in subsequent tutorial pages.
Posted : 15/07/2020 5:11 pm