SQLBolt：https://sqlbolt.com/lesson/introduction

• SQL Lesson 6: Multi-table queries with JOINs

使用語法 & 概念

注意順序：FROM JOIN 等語法，是確定資料來源與資料關聯性的基礎，在解讀和結構上會放在 WHERE ORDER BY LIMIT 之前。

下述語法解讀順序：
FROM & JOIN >> WHERE >> SELECT >>ORDER BY >> LIMIT

• JOIN / INNER JOIN

  /* Select query with INNER JOIN on multiple tables */
  SELECT column, another_table_column, …
  FROM mytable
  INNER JOIN another_table 
      ON mytable.id = another_table.id
  WHERE condition(s)
  ORDER BY column, … ASC/DESC
  LIMIT num_limit OFFSET num_offset;
  

• 資料庫正規化（Database Normalization）

  Database normalization is useful because it minimizes duplicate data in any single table, and allows for data in the database to grow independently of each other (ie. Types of car engines can grow independent of each type of car). As a trade-off, queries get slightly more complex since they have to be able to find data from different parts of the database, and performance issues can arise when working with many large tables.

  In order to answer questions about an entity that has data spanning multiple tables in a normalized database, we need to learn how to write a query that can combine all that data and pull out exactly the information we need.

SQL Lesson 6: Multi-table queries with JOINs

今天的課程，使用的語法是 JOIN ，並且提到了資料庫正規化，我們一樣先來解題吧。

題目 1

--1. Find the domestic and international sales for each movie 
SELECT title, domestic_sales, international_sales
FROM movies JOIN boxoffice
ON movies.id = boxoffice.movie_id;

題目 2

--2. Show the sales numbers for each movie that did better internationally rather than domestically
SELECT title, domestic_sales, international_sales
FROM movies JOIN boxoffice
ON movies.id = boxoffice.movie_id
WHERE international_sales >  domestic_sales;

題目 3

--3. List all the movies by their ratings in descending order
SELECT title, rating
FROM movies JOIN boxoffice
ON movies.id = boxoffice.movie_id
ORDER BY rating DESC;

題解

本章節使用的 JOIN 指的是 INNER JOIN，SQL中單寫JOIN 的話就是INNER JOIN 的用法。

INNER JOIN 為內部合併查詢指令，可以取回2個資料表都共同存在合併欄位的記錄資料。也就是在「兩個資料表都有在 on 關鍵字所指定的欄位的值」這個條件下才取出想取的欄位。

SELECT [想搜尋的欄位(資料表A & B都可)]
FROM [資料表A]
INNER JOIN [資料表B]
ON [資料表A].[關聯鍵] = [資料表B].[關聯鍵]

參考資料：

• SQL Lesson 6: Multi-table queries with JOINs
• MySQL 學習筆記