Tuesday, 26 July 2016

Joins



Joins are powerful, used to pull data from multiple tables. Suppose you had two tables
a.   employee : Contains basic employee information
b.   employee_address : Contains address details of employee.
You can extract employee and address details in same query using joins.

I am going to use following data.

CREATE TABLE employee(
  id int PRIMARY KEY,
  firstName VARCHAR(20),
  lastName VARCHAR(20),
  salary DECIMAL(10, 2)
);

INSERT INTO employee VALUES (1, "Hari Krishna", "Gurram", 12345.67);
INSERT INTO employee VALUES (2, "Rama Devi", "Gurram", 1234578.67);
INSERT INTO employee VALUES (3, "Lakshmana", "Rao", 9876543.67);
INSERT INTO employee VALUES (4, "Rama", "Krishna", 1234587.67);
INSERT INTO employee VALUES (5, "Sowmya", "asd", 1238745.67);
INSERT INTO employee VALUES (6, "Jyotsna", "PS", 76543.67);
INSERT INTO employee VALUES (7, "Gireesh", "Amara", 87698);

CREATE TABLE employee_info(
  id int PRIMARY KEY,
  city VARCHAR(20),
  state VARCHAR(20),
  country VARCHAR(20)
);

INSERT INTO employee_info VALUES (1, "Bangalore", "Karnataka", "India");
INSERT INTO employee_info VALUES (2, "Chirala", "Andhra Pradesh", "India");
INSERT INTO employee_info VALUES (3, "Punuru", "Andhra Pradesh", "India");
INSERT INTO employee_info VALUES (4, "Bangalore", "Karnataka", "India");
INSERT INTO employee_info VALUES (5, "Bangalore", "Karnataka", "India");
INSERT INTO employee_info VALUES (6, "Nellore", "Andhra Pradesh", "India");
INSERT INTO employee_info VALUES (7, "Chennai", "Tamilnadu", "India");

mysql> SELECT * FROM employee;
+----+--------------+----------+------------+
| id | firstName    | lastName | salary     |
+----+--------------+----------+------------+
|  1 | Hari Krishna | Gurram   |   12345.67 |
|  2 | Rama Devi    | Gurram   | 1234578.67 |
|  3 | Lakshmana    | Rao      | 9876543.67 |
|  4 | Rama         | Krishna  | 1234587.67 |
|  5 | Sowmya       | asd      | 1238745.67 |
|  6 | Jyotsna      | PS       |   76543.67 |
|  7 | Gireesh      | Amara    |   87698.00 |
+----+--------------+----------+------------+
7 rows in set (0.00 sec)


mysql> SELECT * FROM employee_info;
+----+-----------+----------------+---------+
| id | city      | state          | country |
+----+-----------+----------------+---------+
|  1 | Bangalore | Karnataka      | India   |
|  2 | Chirala   | Andhra Pradesh | India   |
|  3 | Punuru    | Andhra Pradesh | India   |
|  4 | Bangalore | Karnataka      | India   |
|  5 | Bangalore | Karnataka      | India   |
|  6 | Nellore   | Andhra Pradesh | India   |
|  7 | Chennai   | Tamilnadu      | India   |
+----+-----------+----------------+---------+
7 rows in set (0.00 sec)


Get all employees information including address details.

SELECT *
FROM employee AS emp
JOIN employee_info AS empInfo
ON emp.id = empInfo.id;

mysql> SELECT *
    -> FROM employee AS emp
    -> JOIN employee_info AS empInfo
    -> ON emp.id = empInfo.id;
+----+--------------+----------+------------+----+-----------+----------------+---------+
| id | firstName    | lastName | salary     | id | city      | state          | country |
+----+--------------+----------+------------+----+-----------+----------------+---------+
|  1 | Hari Krishna | Gurram   |   12345.67 |  1 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  4 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  5 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  7 | Chennai   | Tamilnadu      | India   |
+----+--------------+----------+------------+----+-----------+----------------+---------+
7 rows in set (0.01 sec)


You can get the same result following query.
SELECT * FROM employee emp, employee_info empInfo WHERE emp.id = empInfo.id ;



mysql> SELECT * FROM employee emp, employee_info empInfo WHERE emp.id = empInfo.id ;
+----+--------------+----------+------------+----+-----------+----------------+---------+
| id | firstName    | lastName | salary     | id | city      | state          | country |
+----+--------------+----------+------------+----+-----------+----------------+---------+
|  1 | Hari Krishna | Gurram   |   12345.67 |  1 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  4 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  5 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  7 | Chennai   | Tamilnadu      | India   |
+----+--------------+----------+------------+----+-----------+----------------+---------+
7 rows in set (0.00 sec)


When you don’t specify any condition, it return cross product of records from two tables.

mysql> SELECT * FROM employee, employee_info;
+----+--------------+----------+------------+----+-----------+----------------+---------+
| id | firstName    | lastName | salary     | id | city      | state          | country |
+----+--------------+----------+------------+----+-----------+----------------+---------+
|  1 | Hari Krishna | Gurram   |   12345.67 |  1 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  1 | Bangalore | Karnataka      | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  1 | Bangalore | Karnataka      | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  1 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  1 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  1 | Bangalore | Karnataka      | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  1 | Bangalore | Karnataka      | India   |
|  1 | Hari Krishna | Gurram   |   12345.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  2 | Chirala   | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  2 | Chirala   | Andhra Pradesh | India   |
|  1 | Hari Krishna | Gurram   |   12345.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  3 | Punuru    | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  3 | Punuru    | Andhra Pradesh | India   |
|  1 | Hari Krishna | Gurram   |   12345.67 |  4 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  4 | Bangalore | Karnataka      | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  4 | Bangalore | Karnataka      | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  4 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  4 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  4 | Bangalore | Karnataka      | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  4 | Bangalore | Karnataka      | India   |
|  1 | Hari Krishna | Gurram   |   12345.67 |  5 | Bangalore | Karnataka      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  5 | Bangalore | Karnataka      | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  5 | Bangalore | Karnataka      | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  5 | Bangalore | Karnataka      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  5 | Bangalore | Karnataka      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  5 | Bangalore | Karnataka      | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  5 | Bangalore | Karnataka      | India   |
|  1 | Hari Krishna | Gurram   |   12345.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  6 | Nellore   | Andhra Pradesh | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  6 | Nellore   | Andhra Pradesh | India   |
|  1 | Hari Krishna | Gurram   |   12345.67 |  7 | Chennai   | Tamilnadu      | India   |
|  2 | Rama Devi    | Gurram   | 1234578.67 |  7 | Chennai   | Tamilnadu      | India   |
|  3 | Lakshmana    | Rao      | 9876543.67 |  7 | Chennai   | Tamilnadu      | India   |
|  4 | Rama         | Krishna  | 1234587.67 |  7 | Chennai   | Tamilnadu      | India   |
|  5 | Sowmya       | asd      | 1238745.67 |  7 | Chennai   | Tamilnadu      | India   |
|  6 | Jyotsna      | PS       |   76543.67 |  7 | Chennai   | Tamilnadu      | India   |
|  7 | Gireesh      | Amara    |   87698.00 |  7 | Chennai   | Tamilnadu      | India   |
+----+--------------+----------+------------+----+-----------+----------------+---------+
49 rows in set (0.00 sec)



Extract employeeId, salary, country.


SELECT emp.id, emp.salary, empInfo.country
FROM employee AS emp
JOIN employee_info AS empInfo
ON emp.id = empInfo.id;    

mysql> SELECT emp.id, emp.salary, empInfo.country
    -> FROM employee AS emp
    -> JOIN employee_info AS empInfo
    -> ON emp.id = empInfo.id;
+----+------------+---------+
| id | salary     | country |
+----+------------+---------+
|  1 |   12345.67 | India   |
|  2 | 1234578.67 | India   |
|  3 | 9876543.67 | India   |
|  4 | 1234587.67 | India   |
|  5 | 1238745.67 | India   |
|  6 |   76543.67 | India   |
|  7 |   87698.00 | India   |
+----+------------+---------+
7 rows in set (0.00 sec)









Previous                                                 Next                                                 Home

No comments:

Post a Comment