From Clause in SQL

Introduction

Let us take a simple scenario where you are asked to bring a file containing some important papers. What should your first question be? Obviously, FROM where should you bring it, right?

The same thing happens in the case of an SQL compiler. When you ask it to fetch some entries, you need to tell it FROM where should it bring them, i.e., from which table

So, precisely, FROM is an SQL keyword that tells the SQL compiler from which table(s) it should get the entries when a query is made.

Syntax

• The syntax for a single table in FROM clause:

• The syntax for using multiple tables in FROM clause with JOIN conditions:

  OR

Examples

Let us take a few examples to see how the FROM clause is used with a single table and with multiple tables using the JOIN conditions.

One table in the FROM clause

Let us consider a table named EMPLOYEE having the columns employee ID (empId), employee name (name), and department (dept), and the empId is the PRIMARY KEY. The table has 5 entries and it goes as follows:

EMPLOYEE table

Now, say, we want the names of the employees who work in the “Sales” department.

So, here is how we use the FROM clause in the case we need data from a single table:

The above MySQL query finds the names of the employees who work in the "Sales" department from the EMPLOYEE table.

Output:

We get only the names of Naren and Ava as these are the only two employees who work in the Sales department.

Two tables in the FROM clause

Now, let us consider an example where we have two separate tables. One table is named EMPLOYEE which consists of employee details like employee ID, employee name, and address in columns named Emp_Id, Name, and Address respectively. Another table named OFFICE consists of office details like employee ID, department, and the office locations in columns named Emp_Id, Dept, Location respectively.

In both the tables, we have Emp_Id as PRIMARY KEY and each of them has 3 entries.

EMPLOYEE table:

OFFICE table:

We shall use the above two tables and understand examples with both INNER JOIN and OUTER JOIN.

Two tables in the FROM clause (INNER JOIN)

Let us try to find the details of the employee whose office location is the same as their address. Here, we will need both the tables. So, we shall be using FROM clause on two tables and apply INNER JOIN.

The MySQL query goes as follows:

The above MySQL code finds the details of the employees whose address is the same as their office location by joining the two tables EMPLOYEE and OFFICE using INNER JOIN.

Output:

And, there we have the details of 'Subham'! He is the only employee who has his office location the same as his address.

Thus, we saw how the FROM clause is used to make queries combining two separate tables and using INNER JOIN.

Next, we will see how it works with OUTER JOIN.

Two tables in the FROM clause (OUTER JOIN)

Now, let us do the same query for finding the details of the employee whose office location is the same as their address but this time we will use OUTER JOIN.

The MySQL query goes as follows:

The above MySQL query finds the details of the employees whose addresses are the same as their office location. Since we need the information from both the tables, the EMPLOYEE table, and the OFFICE table, we join the two using LEFT OUTER JOIN.

Output:

And there we go! We again have Subham who is the only employee whose address is the same as his office location. Alongside we have the two other entries from the left table (EMPLOYEE table) for which there is no match on the right table (OFFICE table). These two extra entries are because of the LEFT OUTER JOIN.

If we use RIGHT OUTER JOIN we will get a pretty similar output. We will get the employee with Emp_Id 2 as it is in the case of LEFT OUTER JOIN. The only difference will be the entries that are present on the right table (OFFICE table) will be present for which there is no match is found on the left table (EMPLOYEE table).

The MySQL query and output using RIGHT OUTER JOIN are as follows:

Output:

Read More:

• Difference Between SQL and NoSQL.