Summary -

In this topic, we described about the FROM Clause with detailed example.

FROM clause is used to specify table name from which we are trying to fetch data using the SELECT statement. FROM clause is also used to specify subquery. More than one table can be directly specified by separating with a comma (,) using FROM clause. While executing the query, system first validates the FROM clause rather than checking SELECT or DELETE statement.

Syntax -

FROM table1
[ { INNER JOIN 
   | LEFT [OUTER] JOIN
   | RIGHT [OUTER] JOIN
   | FULL [OUTER] JOIN } table2
 ON table1.column1 = table2.column1 ];
  • table1 – Specifies the name of the table1.
  • table2 - Specifies the name of the table2.

Example -

consider employee_details and department_details tables are as follows -

employee_details -

emp_id emp_name designation manager_id date_of_hire salary dept_id
001 Employee1 Director 2019-07-11 45000.00 1000
002 Employee2 Director 2019-07-11 40000.00 2000
003 Employee3 Manager Employee1 2019-07-11 27000.00 1000
004 Employee4 Manager Employee2 2019-10-08 25000.00 2000
005 Employee5 Analyst Employee3 2019-07-11 20000.00 1000
006 Employee6 Analyst Employee3 2019-10-08 18000.00 1000
007 Employee7 Clerk Employee3 2019-07-11 15000.00 1000
008 Employee8 Salesman Employee4 2019-09-09 14000.00 2000
009 Employee9 Salesman Employee4 2019-10-08 13000.00 2000

department_details -

dept_id dept_name dept_location
1000 Office Location1
2000 Sales Location2

Scenario – Fetch details from table.

Requirement – Fetch sales department employee details. The query was as follows –

 SELECT emp_name FROM employee_details WHERE dept_id = "2000"; 

By executing above query, we can get results as shown below –

emp_name
Employee2
Employee4
Employee8
Employee9

Scenario – Fetch matching details from both tables using Subquery.

Requirement – Fetch employees working in location1 from employee_details table. The query was as follows –

SELECT emp_id, emp_name, salary, dept_id FROM employee_details
WHERE dept_id 
IN (SELECT dept_id FROM department_details WHERE dept_location =
 'Location1'); 

By executing above query, we can get results as shown below -

emp_id emp_name salary dept_id
001 Employee1 45000.00 1000
003 Employee3 27000.00 1000
005 Employee5 20000.00 1000
006 Employee6 18000.00 1000
007 Employee7 15000.00 1000

Scenario – Fetch matching details from both tables using INNER JOIN.

Requirement – Fetch emp_id, emp_name, salary from employee_details and their corresponding dept_location from department_details tables. The query was as follows –

SELECT employee_details.emp_name, employee_details.emp_id,
employee_details.Salary, department_details.dept_location
FROM employee_details INNER JOIN department_details ON 
employee_details.dept_id = department_details.dept_id; 

By executing above query, we can get results as shown below –

emp_id emp_name salary dept_location
001 Employee1 45000.00 Location1
002 Employee2 40000.00 Location2
003 Employee3 27000.00 Location1
004 Employee4 25000.00 Location2
005 Employee5 20000.00 Location1
006 Employee6 18000.00 Location1
007 Employee7 15000.00 Location1
008 Employee8 14000.00 Location2
009 Employee9 13000.00 Location2

Scenario – Fetch matching details from both tables using OUTER JOIN.

Requirement1– Fetch emp_id, emp_name, salary from employee_details and their corresponding dept_name from department_details tables. The query was as follows –

SELECT employee_details.emp_name, employee_details.emp_id,
employee_details.salary, department_details.dept_name
FROM employee_details LEFT OUTER JOIN department_details
ON employee_details.dept_id = department_details.dept_id;  

By executing above query, we can get results as shown below -

emp_id emp_name salary dept_name
001 Employee1 45000.00 Office
002 Employee2 40000.00 Sales
003 Employee3 27000.00 Office
004 Employee4 25000.00 Sales
005 Employee5 20000.00 Office
006 Employee6 18000.00 Office
007 Employee7 15000.00 Office
008 Employee8 14000.00 Sales
009 Employee9 13000.00 Sales

Requirement2 – Fetch emp_id, emp_name, salary from employee_details and their corresponding dept_name from department_details tables. The query was as follows –

SELECT employee_details.emp_name, employee_details.emp_id,
employee_details.Salary, department_details. dept_name 
FROM employee_details RIGHT OUTER JOIN department_details
ON employee_details.dept_id = department_details.dept_id; 

By executing above query, we can get results as shown below –

emp_id emp_name salary dept_name
001 Employee1 45000.00 Office
003 Employee3 27000.00 Office
005 Employee5 20000.00 Office
006 Employee6 18000.00 Office
007 Employee7 15000.00 Office
002 Employee2 40000.00 Sales
004 Employee4 25000.00 Sales
008 Employee8 14000.00 Sales
009 Employee9 13000.00 Sales