MySQL Join – Results from Multiple Tables


MySQL join is used to fetch results from multiple tables. The general syntax of MySQL Join is as follows

SELECT * | column1, column2.......
FROM table1, table2........

Sample Databasde Tables to demonstrate MySQL join

mysql> select * from student;
+-------+---------+--------+-------+-------+---------+--------+--------+
| admno | name    | stream | marks | grade | fees    | gender | gameID |
+-------+---------+--------+-------+-------+---------+--------+--------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |
+-------+---------+--------+-------+-------+---------+--------+--------+
12 rows in set (0.00 sec)

mysql> select * from games;
+----+----------+---------+---------------+
| id | name     | fees    | coach         |
+----+----------+---------+---------------+
|  1 | Cricket  | 2000.00 | RK Sharma     |
|  2 | Football | 1800.00 | Deepak Rana   |
|  3 | Sketing  | 1500.00 | Vikas Pandey  |
|  4 | Boxing   | 1650.00 | Mayank Sharma |
+----+----------+---------+---------------+

Primary Keys and Foreign Key

Admno is the primary key in the STUDENT table and GameID is the foreign key as its values are coming from the Table Games where their value is acting as a Primary Key.

Cross Join /Arbitrary Join

This type of join is performed when the rows of the first table are multiplied by the rows of the second table and columns of both tables are added.
The Cartesian product, also known as cross-join, returns all the rows in all the tables listed in the query.  Each row in the first table is paired with all the rows in the second table.

MySQL Join

SQL command to perform Cross Join

mysql> select * from student,games;
+-------+---------+--------+-------+-------+---------+--------+--------+----+----------+---------+---------------+
| admno | name    | stream | marks | grade | fees    | gender | gameID | id | name     | fees    | coach         |
+-------+---------+--------+-------+-------+---------+--------+--------+----+----------+---------+---------------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |  2 | Football | 1800.00 | Deepak Rana   |
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |  2 | Football | 1800.00 | Deepak Rana   |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |  2 | Football | 1800.00 | Deepak Rana   |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |  2 | Football | 1800.00 | Deepak Rana   |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |  2 | Football | 1800.00 | Deepak Rana   |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |  2 | Football | 1800.00 | Deepak Rana   |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |  2 | Football | 1800.00 | Deepak Rana   |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |  2 | Football | 1800.00 | Deepak Rana   |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |  2 | Football | 1800.00 | Deepak Rana   |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |  2 | Football | 1800.00 | Deepak Rana   |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |  2 | Football | 1800.00 | Deepak Rana   |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |  2 | Football | 1800.00 | Deepak Rana   |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |  4 | Boxing   | 1650.00 | Mayank Sharma |
+-------+---------+--------+-------+-------+---------+--------+--------+----+----------+---------+---------------+
48 rows in set (0.00 sec)

Equi-Join

When Two tables are joined on the equality of the value of two columns of the tables, It is called Equi-join. Equi-join returns all the rows that have the matching values only.
MySQL command to perform equi-join

mysql> select * from student,games
    -> where gameId = id;
+-------+---------+--------+-------+-------+---------+--------+--------+----+----------+---------+---------------+
| admno | name    | stream | marks | grade | fees    | gender | gameID | id | name     | fees    | coach         |
+-------+---------+--------+-------+-------+---------+--------+--------+----+----------+---------+---------------+
| 101   | rakesh  | sci    | 96.56 | A     | 1850.35 | F      |      2 |  2 | Football | 1800.00 | Deepak Rana   |
| 102   | udit    | sci    | 56.50 | B     | 1850.35 | M      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 103   | mohit   | bio    | 46.50 | C     | 1980.90 | M      |      3 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 104   | mannat  | human  | 67.50 | A     | 1420.15 | F      |      4 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 105   | unnati  | comm   | 77.50 | B     | 1650.75 | F      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 106   | nikunj  | sci    | 79.50 | A     | 1850.35 | M      |      3 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 107   | pushkar | comm   | 54.20 | C     | 1650.75 | M      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 108   | pratham | comm   | 84.80 | A     | 1650.75 | M      |      4 |  4 | Boxing   | 1650.00 | Mayank Sharma |
| 109   | mohit   | comm   | 64.80 | B     | 1650.75 | M      |      2 |  2 | Football | 1800.00 | Deepak Rana   |
| 110   | urshavi | bio    | 96.80 | A     | 1980.90 | M      |      3 |  3 | Sketing  | 1500.00 | Vikas Pandey  |
| 111   | punya   | comm   | 86.56 | B     | 1650.75 | F      |      1 |  1 | Cricket  | 2000.00 | RK Sharma     |
| 112   | vipul   | comm   |  NULL | A     | 1650.75 | M      |      2 |  2 | Football | 1800.00 | Deepak Rana   |
+-------+---------+--------+-------+-------+---------+--------+--------+----+----------+---------+---------------+
12 rows in set (0.00 sec)

Natural Join

IN the above example, perhaps you have noticed that the gameID, id has the same values. Same way student name and Game Name has the same column name that is not natural. In order to achieve a natural table, we remove one ID and rename the column Names.

Example of Natural Join

mysql> select admno,s.name 'Student Name',stream,marks,s.fees 'Tution Fees',gender,g.name 'Game Name',g.fees 'Game Fees',coach
    -> from student s, games g
    -> where gameid = id;
+-------+--------------+--------+-------+-------------+--------+-----------+-----------+---------------+
| admno | Student Name | stream | marks | Tution Fees | gender | Game Name | Game Fees | coach         |
+-------+--------------+--------+-------+-------------+--------+-----------+-----------+---------------+
| 101   | rakesh       | sci    | 96.56 |     1850.35 | F      | Football  |   1800.00 | Deepak Rana   |
| 102   | udit         | sci    | 56.50 |     1850.35 | M      | Cricket   |   2000.00 | RK Sharma     |
| 103   | mohit        | bio    | 46.50 |     1980.90 | M      | Sketing   |   1500.00 | Vikas Pandey  |
| 104   | mannat       | human  | 67.50 |     1420.15 | F      | Boxing    |   1650.00 | Mayank Sharma |
| 105   | unnati       | comm   | 77.50 |     1650.75 | F      | Cricket   |   2000.00 | RK Sharma     |
| 106   | nikunj       | sci    | 79.50 |     1850.35 | M      | Sketing   |   1500.00 | Vikas Pandey  |
| 107   | pushkar      | comm   | 54.20 |     1650.75 | M      | Cricket   |   2000.00 | RK Sharma     |
| 108   | pratham      | comm   | 84.80 |     1650.75 | M      | Boxing    |   1650.00 | Mayank Sharma |
| 109   | mohit        | comm   | 64.80 |     1650.75 | M      | Football  |   1800.00 | Deepak Rana   |
| 110   | urshavi      | bio    | 96.80 |     1980.90 | M      | Sketing   |   1500.00 | Vikas Pandey  |
| 111   | punya        | comm   | 86.56 |     1650.75 | F      | Cricket   |   2000.00 | RK Sharma     |
| 112   | vipul        | comm   |  NULL |     1650.75 | M      | Football  |   1800.00 | Deepak Rana   |
+-------+--------------+--------+-------+-------------+--------+-----------+-----------+---------------+
12 rows in set (0.00 sec)

NOTE :

  • Always Use a fully qualified column name when the same name is shared in more than one table.
  • used Table alias to reduce redundant typing of table names.
Print Friendly, PDF & Email

Related Posts

If you like CBSEToaday and would like to contribute, you can also write an article using submit article or mail your article to contribute@cbsetoday.com See your article appearing on the cbsetoday.com main page and help other students/teachers.