From Clause in SQL

Learn via video course
FREE
View all courses
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning
DBMS Course - Master the Fundamentals and Advanced Concepts
DBMS Course - Master the Fundamentals and Advanced Concepts
by Srikanth Varma
1000
5
Start Learning

Overview

The FROM clause in SQL is used to tell the compiler from which table(s) the data needs to be fetched when an SQL query is made.

In this article, we will learn about the various ways of using the FROM clause with single or multiple tables.

Note: This article will use MySQL syntaxes although the FROM clause can be used in other RDBMS like Oracle Database, PostgreSQL, Microsoft SQL Server, etc the syntax might be a bit different.

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

Note: Well, it can even be a case where you need to get the entries from a combination of a few tables, in that case, we use JOIN.]{.note}

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.

FROM clause cannot be used alone, we need a bare minimum SELECT clause before it, otherwise, no result will be returned.

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:

Conclusion

  • FROM clause is used to specify the table(s) from which the data needs to be fetched.
  • We can use multiple tables in the FROM clause by using JOINs like INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, or NATURAL JOIN.

Read More: