[Udemy] 15 Days of SQL - Day 6 ① : JOINS(Inner join, Outer join, Left outer join, Right outer join

JOIN : combine information from multiple tables in one query

JOIN 5줄 요약

• 조인은 두 개의 테이블을 서로 묶어서 하나의 결과를 만들어 내는 것을 말한다.
• INNER JOIN(내부 조인)은 두 테이블을 조인할 때, 두 테이블에 모두 지정한 열의 데이터가 있어야 한다.
• OUTER JOIN(외부 조인)은 두 테이블을 조인할 때, 1개의 테이블에만 데이터가 있어도 결과가 나온다.
• CROSS JOIN(상호 조인)은 한쪽 테이블의 모든 행과 다른 쪽 테이블의 모든 행을 조인하는 기능이다.
• SELF JOIN(자체 조인)은 자신이 자신과 조인한다는 의미로, 1개의 테이블을 사용한다.

 

1. Inner join

• Combin the two tables in one query
• Always need a common column/reference - join column
• Only rows where reference column value is in both tables
• Order of tables (A and B/ B and A) does not matter
• Repeated values in either table will also be repeated
• Syntax

1)
SELECT * FROM TableA
INNER JOIN TableB
ON TableA.employee(column name) = TableB.employee(column name)

2)
SELECT * FROM TableA A
INNER JOIN TableB B
ON A.employee = B.employee

3)
SELECT A.employee FROM TableA A
INNER JOIN TableB B
ON A.employee = B.employee

-- Aliases help with writing & reading the code more easily

SELECT payment.*, first_name, last_name -- every column in payment table & 'first_name', 'last_name' column in customer table
FROM payment
INNER JOIN customer
ON payment.customer_id = customer.customer_id

 

- if the column is in both tables, specify where it belongs to

 

SELECT payment_id,
pa.customer_id,
amount,
first_name,
last_name
FROM payment pa
INNER JOIN customer cu
ON pa.customer_id = cu.customer_id

 

* Challenge

Write a query to know which category the airline company sells the most tikets. (category : Business, Economy, Comfort)

SELECT 
fare_conditions,
COUNT(*)
FROM boarding_passes b
INNER JOIN seats s
ON s.seat_no = b.seat_no
GROUP BY fare_conditions

 

Difficult to solve, need to check it over again

 

 

 

 

 

---

 

https://hongong.hanbit.co.kr/sql-%EA%B8%B0%EB%B3%B8-%EB%AC%B8%EB%B2%95-joininner-outer-cross-self-join/ 

SQL 기본 문법: JOIN(INNER, OUTER, CROSS, SELF JOIN)

 

2. Full Outer Join

SELECT * FROM TableA
FULL OUTER JOIN TableB
ON TableA.employee(column name) = TableB.employee(column name)

 

SELECT * FROM boarding_passes b
FULL OUTER JOIN tickets t
ON b.ticket_no = t.ticket_no
WHERE b.tickets_no IS null

-- find data where there isn't ticket_no on both boarding_passes and tickets tables

SELECT COUNT(*) FROM boarding_passes b
FULL OUTER JOIN tickets t
ON b.ticket_no = t.ticket_no
WHERE b.tickets_no IS null

-- count how many tickets that don't have ticket numbers

 

3. LEFT OUTER JOIN

Find how many flights these aircrafts have been used

Left table : all aircrafts - outputput(default)

Right table : flights

SELECT * FROM aircrafts_data a
LEFT JOIN flights f
ON a.aircraft_code = f.aircraft_code

 

Find all aircrafts that have not been used in any flight.

SELECT * FROM aircrafts_data a
LEFT JOIN flights f
ON a.aircraft_code = f.aircraft_code
WHERE f.flight_id IS null

 

* Challenge

1. Write a query to find out which seats are the most popular - try to find out which seat has been chosen most frequently.

    Make sure all seats are included even if they have never been booked.

SELECT
s.seat_no,
COUNT(*)
FROM seats s
LEFT JOIN boarding_passes b 
ON s.seat_no = b.seat_no
GROUP BY s.seat_no -- doesn't matter 
ORDER BY COUNT(*) DESC
--WHERE b.seat_no is null

2. Are there seats that have never been booked?

 

3. Try to find which line (A, B, .., H) has been chosen most frequently.

SELECT
RIGHT(s.seat_no, 1) AS line,
COUNT(*)
FROM seats s
LEFT JOIN boarding_passes b
ON b.seat_no = s.seat_no
GROUP BY RIGHT(s.seat_no, 1)
ORDER BY COUNT(*) DESC

 

---

4. RIGHT OUTER JOIN

• Rows that are only in the first table will result in the same.
• Left outer join and Right outer join is interchangeable, so left outer join is more commonly used.
• Syntax

1)
SELECT * FROM TableA
RIGHT OUTER JOIN TableB
ON TableA.employee(column name) = TableB.employee(column name)

2)
SELECT * FROM TableB
LEFT OUTER JOIN TableA
ON TableA.employee(column name) = TableB.employee(column name)

-- 1) and 2) will result the same

 

* Challenge

1. The company wants to run a phone call campaining on all customers in Texas(=district).

 What are the customers (first_name, last_name, phone number and their district) from Texas?

SELECT
first_name,
last_name,
phone,
district
FROM customer c
LEFT JOIN address a
ON c.address_id = a.address_id
WHERE district = 'Texas'

 

2. Are there any (old) addresses that are not related to any customer?

SELECT * FROM address a
LEFT JOIN customer c
ON c.address_id = a.address_id
WHERE c.customer_id is null

-- this data is only seen in address table