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)
No comments:
Post a Comment