SQL SELECT JOIN

JOIN command in SQL allow you to retrieve data from more than one table and mix and match them.
The structure  is the following:
SELECT <alias1>.<column1>, <alilas2>.<column2> FROM <tableName1> AS <alias1> JOIN <table2> AS <alias2> ON <alias1>.<matchingColumn1> = <alias2>.<matchingColumn2>;
After the “ON” we put the matching condition that tell the SQL how to match rows from one tables into rows from the other table and eventually create one row for every row from the first table and the second table.

So for example lets say we have the following information:


MariaDB [firstDB]> SELECT * FROM employee;
+------------+-----------+-----------------+--------+
| first_name | last_name | employee_number | salary |
+------------+-----------+-----------------+--------+
| Julia      | Roberts   |               1 |  50000 |
| Edison     | Ford      |               2 |  45000 |
| Irina      | Shayk     |               3 |  60000 |
| Yigal      | Arnon     |               4 |  29000 |
+------------+-----------+-----------------+--------+
4 rows in set (0.00 sec)

MariaDB [firstDB]> SELECT * FROM manager;
\+------------+-----------+----------+
| first_name | last_name | managing |
+------------+-----------+----------+
| yoel       | Eliaran   |        1 |
| Bob        | Ross      |        2 |
| Thomas     | Kinkade   |        3 |
| Ania       | Kozicka   |        4 |
+------------+-----------+----------+
4 rows in set (0.03 sec)

So in our case the managing column in the manager table is the employee number, that the manager is managing.
So in order to see for every employee who is his manager:


MariaDB [firstDB]> SELECT e.first_name,e.last_name,m.first_name,m.last_name FROM employee AS e JOIN manager AS m ON e.employee_number = m.managing;
+------------+-----------+------------+-----------+
| first_name | last_name | first_name | last_name |
+------------+-----------+------------+-----------+
| Julia      | Roberts   | yoel       | Eliaran   |
| Edison     | Ford      | Bob        | Ross      |
| Irina      | Shayk     | Thomas     | Kinkade   |
| Yigal      | Arnon     | Ania       | Kozicka   |
+------------+-----------+------------+-----------+
4 rows in set (0.02 sec)

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s