03_内部結合

概要

• 内部結合についてまとめる。

内部結合(INNER_JOIN)

概要

• 複数のテーブルからキーが一致するレコードだけを取り出す。
• 「条件による対応付け」と「列名による対応付け」の二種類がある。

文法

条件による対応付け

INNER JOIN 結合するテーブル名 ON 条件

列名による対応付け

INNER JOIN 結合するテーブル名 USING(共通の列名)

サンプル

事前準備

使用するデータベースを変更

USE world

• 実行結果

MariaDB [dekirusample2]> USE world;
Database changed

都市の名前と国コードを表示

SELECT Name, CountryCode FROM city LIMIT 3;

• 実行結果

MariaDB [world]> SELECT Name, CountryCode FROM city LIMIT 3;
+----------+-------------+
| Name     | CountryCode |
+----------+-------------+
| Kabul    | AFG         |
| Qandahar | AFG         |
| Herat    | AFG         |
+----------+-------------+
3 rows in set (0.03 sec)

国名と国コードを表示

SELECT Name, ContryCode FROM country WHERE CountryCode = 'AFG';

• 実行結果

MariaDB [world]> SELECT Name, Code FROM country WHERE Code = 'AFG';
+-------------+------+
| Name        | Code |
+-------------+------+
| Afghanistan | AFG  |
+-------------+------+
1 row in set (0.01 sec)

都市の名前と国名を表示

SELECT city.Name, country.Name 
FROM city INNER JOIN country ON 
city.CountryCode = country.Code 
LIMIT 3;

• 実行結果

MariaDB [world]> SELECT city.Name, country.Name FROM city INNER JOIN country ON city.CountryCode = country.Code LIMIT 3;
+------------+-------------+
| Name       | Name        |
+------------+-------------+
| Oranjestad | Aruba       |
| Kabul      | Afghanistan |
| Qandahar   | Afghanistan |
+------------+-------------+
3 rows in set (0.00 sec)

列に別名を命名

SELECT city.Name AS city, country.Name As country 
FROM city INNER JOIN country ON 
city.CountryCode = country.Code 
LIMIT 3;

• 実行結果

MariaDB [world]> SELECT city.Name AS city, country.Name As country FROM city INNER JOIN country ON city.CountryCode = country.Code LIMIT 3;
+------------+-------------+
| city       | country     |
+------------+-------------+
| Oranjestad | Aruba       |
| Kabul      | Afghanistan |
| Qandahar   | Afghanistan |
+------------+-------------+
3 rows in set (0.00 sec)

テーブルに別名を命名

SELECT ci.Name AS city, co.Name As country 
FROM city AS ci INNER JOIN country AS co ON 
ci.CountryCode = co.Code 
LIMIT 3;

• 実行結果

MariaDB [world]> SELECT ci.Name AS city, co.Name As country FROM city AS ci INNER JOIN country AS co ON ci.CountryCode = co.Code LIMIT 3;
+------------+-------------+
| city       | country     |
+------------+-------------+
| Oranjestad | Aruba       |
| Kabul      | Afghanistan |
| Qandahar   | Afghanistan |
+------------+-------------+
3 rows in set (0.00 sec)

結合した列から条件で抽出

SELECT ci.Name AS city, co.Name As country, ci.Population AS Population 
FROM city AS ci INNER JOIN country AS co ON 
ci.CountryCode = co.Code 
WHERE ci.Population > 9000000;

• 実行結果

MariaDB [world]> SELECT ci.Name AS city, co.Name As country, ci.Population AS Population FROM city AS ci INNER JOIN country AS co ON ci.CountryCode = co.Code
 WHERE ci.Population > 9000000;
+-----------------+-------------+------------+
| city            | country     | Population |
+-----------------+-------------+------------+
| S?o Paulo       | Brazil      |    9968485 |
| Shanghai        | China       |    9696300 |
| Jakarta         | Indonesia   |    9604900 |
| Mumbai (Bombay) | India       |   10500000 |
| Seoul           | South Korea |    9981619 |
| Karachi         | Pakistan    |    9269265 |
+-----------------+-------------+------------+
6 rows in set (0.03 sec)

---

列名で結合

事前準備

USE sakila

• 実行結果

MariaDB [world]> USE sakila
Database changed

列名で結合

SELECT c.first_name, c.last_name 
FROM rental AS r 
INNER JOIN customer AS c 
USING(customer_id) 
WHERE return_date IS NULL 
LIMIT 3;

• 実行結果

MariaDB [sakila]> SELECT c.first_name, c.last_name FROM rental AS r INNER JOIN customer AS c USING(customer_id) WHERE return_date IS NULL LIMIT 3;
+------------+-----------+
| first_name | last_name |
+------------+-----------+
| ELIZABETH  | BROWN     |
| MARGARET   | MOORE     |
| LISA       | ANDERSON  |
+------------+-----------+
3 rows in set (0.00 sec)