MySQL table joins

Till now, we’ve used SELECT to retrieve data from only one table. However, we can extract data from two or more tables using a single SELECT statement.
The strength of RDBMS lies in allowing us to relate data from one table with data from another. This correlation can only be made if atleast one column in the two tables contain related data.
In our example, the columns that contain related data are emp_id of employee_data and e_id of employee_per.

Let’s conduct a table join and extract the names (from employee_data) and spouse names (from employee_per) of married employee.

select CONCAT(f_name, " ", l_name) AS Name,
s_name as 'Spouse Name' from
employee_data, employee_per
where m_status = 'Y' AND
emp_id = e_id;

+-----------------+-----------------+
| Name            | Spouse Name     |
+-----------------+-----------------+
| Manish Sharma   | Anamika Sharma  |
| John Hagan      | Jane Donner     |
| Ganesh Pillai   | Sandhya Pillai  |
| Anamika Sharma  | Manish Sharma   |
| John MacFarland | Mary Shelly     |
| Alok Nanda      | Manika Nanda    |
| Paul Simon      | Muriel Lovelace |
| Arthur Hoopla   | Rina Brighton   |
| Kim Hunter      | Matt Shikari    |
| Danny Gibson    | Betty Cudly     |
| Mike Harper     | Stella Stevens  |
| Monica Sehgal   | Edgar Alan      |
| Peter Champion  | Ruby Richer     |
+-----------------+-----------------+
13 rows in set (0.00 sec)

The FROM clause takes the names of the two tables from which we plan to extract data. Also, we specify that data has to be retrieved for only those entries where the emp_id and e_id are same.

The names of columns in the two tables are unique. However, this may not true always, in which case we can explicitly specify column names along with table name using the dot notation.

select CONCAT(employee_data.f_name, " ", employee_data.l_name)
AS Name, employee_per.s_name AS 'Spouse Name'
from employee_data, employee_per
where employee_per.m_status = 'Y'
AND employee_data.emp_id = employee_per.e_id;

+-----------------+-----------------+
| Name            | Spouse Name     |
+-----------------+-----------------+
| Manish Sharma   | Anamika Sharma  |
| John Hagan      | Jane Donner     |
| Ganesh Pillai   | Sandhya Pillai  |
| Anamika Sharma  | Manish Sharma   |
| John MacFarland | Mary Shelly     |
| Alok Nanda      | Manika Nanda    |
| Paul Simon      | Muriel Lovelace |
| Arthur Hoopla   | Rina Brighton   |
| Kim Hunter      | Matt Shikari    |
| Danny Gibson    | Betty Cudly     |
| Mike Harper     | Stella Stevens  |
| Monica Sehgal   | Edgar Alan      |
| Peter Champion  | Ruby Richer     |
+-----------------+-----------------+
13 rows in set (0.00 sec)