Leveling up your mind

Notifications
Clear all

Joins  

 
Sohaib
(@sohaib-ops)
Member Admin

Inner Joins:

 

SQL Join Types — SQL Joins Explained

 

  1. JOIN orders
  2. ON customers.id = orders.customer_id;
  3.  
  4. SELECT *
  5. FROM orders
  6. JOIN customers
  7. ON customers.id = orders.customer_id;

-- ARBITRARY JOIN - meaningless, but still possible 

  1. SELECT * FROM customers
  2. JOIN orders ON customers.id = orders.id;

 

Quote
Posted : 06/07/2020 4:47 pm
Sohaib
(@sohaib-ops)
Member Admin

Left Join:

 

SQL LEFT JOIN Operation - Tutorial Republic

 

 

 

 

 

  1. SELECT * FROM customers
  2. LEFT JOIN orders
  3. ON customers.id = orders.customer_id;
  1. SELECT first_name, last_name, order_date, amount
  2. FROM customers
  3. LEFT JOIN orders
  4. ON customers.id = orders.customer_id;
  1. SELECT
  2. first_name,
  3. last_name,
  4. IFNULL(SUM(amount), 0) AS total_spent
  5. FROM customers
  6. LEFT JOIN orders
  7. ON customers.id = orders.customer_id
  8. GROUP BY customers.id
  9. ORDER BY total_spent;
ReplyQuote
Posted : 06/07/2020 5:45 pm
Sohaib
(@sohaib-ops)
Member Admin

Right Join: 

SQL RIGHT JOIN Operation - Tutorial Republic

 

  1. SELECT
  2. IFNULL(first_name,'MISSING') AS first,
  3. IFNULL(last_name,'USER') as last,
  4. order_date,
  5. amount,
  6. SUM(amount)
  7. FROM customers
  8. RIGHT JOIN orders
  9. ON customers.id = orders.customer_id
  10. GROUP BY first_name, last_name;

 

 

 

ReplyQuote
Posted : 07/07/2020 4:15 am
Sohaib
(@sohaib-ops)
Member Admin

https://lh4.googleusercontent.com/nV5AfuMFf6B6ajUPcYYXco2LJyR1hVZEJD8taR72yuRJPzxnzbquia5OeeYQsa3kojrTyZnJNZf-I2ag0KLXfm2cynU2rnswPHYqDZgWZnZVd_uVMGJ_j6spM6EBN9oRDr3O2p1F

ReplyQuote
Posted : 07/07/2020 4:15 am
Sohaib
(@sohaib-ops)
Member Admin
  • (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.

ReplyQuote
Posted : 15/07/2020 5:11 pm