MySQL Joins two or many tables
From w3cyberlearnings
To answer these questions:
- How to join more than two tables in MySql?
- How to join two tables in MySql database?
- How to use multiple joins in Sql?
- How to use left join in MySql?
- How to join multiple tables using outer joins?
- How is outer join different from inner join?
- How is outer join is useful?
- How to use union in MySql?
- What is union in MySQL?
Contents |
Create Database
mysql> create database class; Query OK, 1 row affected (0.00 sec) mysql> use class; Database changed
Create three tables
mysql> CREATE TABLE student
-> (
-> student_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> name VARCHAR(200),
-> age INT );
Query OK, 0 rows affected (0.08 sec)
mysql> CREATE TABLE course
-> (
-> course_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> title VARCHAR(200) NOT NULL,
-> credit INT NOT NULL);
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE takecourse
-> (
-> takecourse_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> course_id INT NOT NULL,
-> student_id INT NOT NULL);
Query OK, 0 rows affected (0.11 sec)
Insert Sample Records
Sample records for student table
mysql> INSERT INTO student values(null,'John',28)
-> ,(null,'Bob',30)
-> ,(null,'Kat',28)
-> ,(null,'Pher',24)
-> ,(null,'Jim',21)
-> ;
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0
Sample records for course table
mysql> INSERT INTO course VALUES(null,'Math100',3),
-> (null,'Math200',3),
-> (null,'Math300',2),
-> (null,'English100',2),
-> (null,'English200',3),
-> (null,'English300',3),
-> (null,'Physic100',3),
-> (null,'Physic200',3),
-> (null,'Scien100',4);
Query OK, 9 rows affected (0.00 sec)
Records: 9 Duplicates: 0 Warnings: 0
Sample records for takecourse table
mysql> INSERT INTO takecourse VALUES(null,1,1),
-> (null,1,2),
-> (null,1,3),
-> (null,1,4),
-> (null,4,1),
-> (null,4,3),
-> (null,4,4),
-> (null,9,2),
-> (null,8,1),
-> (null,8,5),
-> (null,8,3);
Query OK, 11 rows affected (0.00 sec)
Records: 11 Duplicates: 0 Warnings: 0
MySQL join columns
mysql> SELECT st.name,
-> st.age,
-> co.title,
-> co.credit
-> FROM
-> student st,
-> course co,
-> takecourse tc
-> WHERE
-> st.student_id= tc.student_id
-> AND
-> co.course_id = tc.course_id;
Result
+------+------+------------+--------+ | name | age | title | credit | +------+------+------------+--------+ | John | 28 | Math100 | 3 | | John | 28 | English100 | 2 | | John | 28 | Physic200 | 3 | | Bob | 30 | Math100 | 3 | | Bob | 30 | Scien100 | 4 | | Kat | 28 | Math100 | 3 | | Kat | 28 | English100 | 2 | | Kat | 28 | Physic200 | 3 | | Pher | 24 | Math100 | 3 | | Pher | 24 | English100 | 2 | | Jim | 21 | Physic200 | 3 | +------+------+------------+--------+ 11 rows in set (0.01 sec)
MySQL join on subquery
mysql> SELECT
-> tc.takecourse_id,
-> (SELECT c.title FROM course c WHERE c.course_id=tc.course_id)
-> AS course,
-> (SELECT st.name FROM student st WHERE st.student_id=tc.student_id)
-> AS student
-> FROM
-> takecourse tc;
Result
+---------------+------------+---------+ | takecourse_id | course | student | +---------------+------------+---------+ | 1 | Math100 | John | | 2 | Math100 | Bob | | 3 | Math100 | Kat | | 4 | Math100 | Pher | | 5 | English100 | John | | 6 | English100 | Kat | | 7 | English100 | Pher | | 8 | Scien100 | Bob | | 9 | Physic200 | John | | 10 | Physic200 | Jim | | 11 | Physic200 | Kat | +---------------+------------+---------+ 11 rows in set (0.00 sec)
MySql INNER JOIN multiple tables
mysql> SELECT st.name, st.age, co.title,co.credit
-> FROM student st INNER JOIN takecourse tc
-> ON st.student_id = tc.student_id
-> INNER JOIN course co ON co.course_id = tc.course_id;
Result
+------+------+------------+--------+ | name | age | title | credit | +------+------+------------+--------+ | John | 28 | Math100 | 3 | | John | 28 | English100 | 2 | | John | 28 | Physic200 | 3 | | Bob | 30 | Math100 | 3 | | Bob | 30 | Scien100 | 4 | | Kat | 28 | Math100 | 3 | | Kat | 28 | English100 | 2 | | Kat | 28 | Physic200 | 3 | | Pher | 24 | Math100 | 3 | | Pher | 24 | English100 | 2 | | Jim | 21 | Physic200 | 3 | +------+------+------------+--------+ 11 rows in set (0.00 sec)
MySql LEFT OUTER join multiple tables
This will answer how many students do not have any score.
mysql> SELECT * FROM student;
+------------+------+------+
| student_id | name | age |
+------------+------+------+
| 1 | John | 28 |
| 2 | Bob | 30 |
| 3 | Kat | 28 |
| 4 | Pher | 24 |
| 5 | Jim | 21 |
+------------+------+------+
5 rows in set (0.00 sec)
mysql> CREATE TABLE score
-> ( score_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> student_id INT NOT NULL,
-> score DOUBLE NOT NULL);
Query OK, 0 rows affected (0.02 sec)
mysql> INSERT INTO score VALUES(null,1,80),
-> (null,4,83);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> SELECT * FROM score;
+----------+------------+-------+
| score_id | student_id | score |
+----------+------------+-------+
| 1 | 1 | 80 |
| 2 | 4 | 83 |
+----------+------------+-------+
2 rows in set (0.00 sec)
mysql> SELECT st.*,sc.* FROM
-> student st LEFT OUTER JOIN
-> score sc ON st.student_id = sc.student_id;
+------------+------+------+----------+------------+-------+
| student_id | name | age | score_id | student_id | score |
+------------+------+------+----------+------------+-------+
| 1 | John | 28 | 1 | 1 | 80 |
| 2 | Bob | 30 | NULL | NULL | NULL |
| 3 | Kat | 28 | NULL | NULL | NULL |
| 4 | Pher | 24 | 2 | 4 | 83 |
| 5 | Jim | 21 | NULL | NULL | NULL |
+------------+------+------+----------+------------+-------+
5 rows in set (0.00 sec)
MySql RIGHT OUTER join multiple tables
The RIGHT OUTER JOIN is based on the right side table. It is going to answer which subject that is never registered by any students.
mysql> SELECT * FROM course;
+-----------+------------+--------+
| course_id | title | credit |
+-----------+------------+--------+
| 1 | Math100 | 3 |
| 2 | Math200 | 3 |
| 3 | Math300 | 2 |
| 4 | English100 | 2 |
| 5 | English200 | 3 |
| 6 | English300 | 3 |
| 7 | Physic100 | 3 |
| 8 | Physic200 | 3 |
| 9 | Scien100 | 4 |
+-----------+------------+--------+
9 rows in set (0.00 sec)
mysql> SELECT
-> tc.takecourse_id,
-> tc.course_id,
-> tc.student_id,
-> c.title,
-> c.credit
-> FROM
-> takecourse tc
-> RIGHT OUTER JOIN
-> course c
-> ON
-> tc.course_id = c.course_id
-> ;
+---------------+-----------+------------+------------+--------+
| takecourse_id | course_id | student_id | title | credit |
+---------------+-----------+------------+------------+--------+
| 1 | 1 | 1 | Math100 | 3 |
| 2 | 1 | 2 | Math100 | 3 |
| 3 | 1 | 3 | Math100 | 3 |
| 4 | 1 | 4 | Math100 | 3 |
| NULL | NULL | NULL | Math200 | 3 |
| NULL | NULL | NULL | Math300 | 2 |
| 5 | 4 | 1 | English100 | 2 |
| 6 | 4 | 3 | English100 | 2 |
| 7 | 4 | 4 | English100 | 2 |
| NULL | NULL | NULL | English200 | 3 |
| NULL | NULL | NULL | English300 | 3 |
| NULL | NULL | NULL | Physic100 | 3 |
| 9 | 8 | 1 | Physic200 | 3 |
| 10 | 8 | 5 | Physic200 | 3 |
| 11 | 8 | 3 | Physic200 | 3 |
| 8 | 9 | 2 | Scien100 | 4 |
+---------------+-----------+------------+------------+--------+
16 rows in set (0.00 sec)
Right Join vs. Left Join vs. Outer Join
Right join will get the query result based on the right table, everything is matched on the right table will be list and list as null when mismatch for the left table. It is opposite for the LEFT JOIN. We can use RIGHT OUTER JOIN = RIGHT JOIN, LEFT OUTER JOIN = LEFT JOIN.
