FROM clause opens up your search query to accessing data tables within your database. The values that FROM accepts are specific data table names, a sub-query (which I will cover in detail in a future guide) and table joins. Similar to the SELECT clause, FROM allows you to alias the data table name to something more memorable with the use of a space after the table reference or the use of the reserved word AS. In the following examples I will show you the various uses of the FROM clause within your SQL query.
Let us say we are tasked with looking at with understanding the departments and employees associated with those departments within our organization.
department table has the following data:
We can query this table with the following statement:
SELECT * FROM department;
If we wanted to alias the table differently, then we can do so like below:
SELECT * FROM department depart; -- Or we can use "AS" SELECT * FROM department AS depart;
If there were more tables involved, aliasing is a great short-hand way to select columns from specific tables, like
Note: If you reference the actual table name after providing it an alias then an error will be thrown. When you alias a table name, you need to reference that alias value for all instances it is used in the query.
Beyond the standard table reference, you can also use the FROM clause to reference a
sub-query. The topic of sub-queries is more advanced and covered in a future guide, but I will leave you an example to familiarize you with what this looks like as it is common to come across in more in-depth data analysis.
The following query involving a sub-query:
SELECT * FROM ( SELECT * FROM department WHERE name IN ('Marketing', 'Sales') ) department;
Would return the following data set:
Finally the most common inclusion (add-on) to the FROM clause in sql analysis is the ability to join additional tables. In a separate guide I will go into the differences of these table joins, but I will leave you with an example so you can familiarize yourself with the common format.
This example contains a separate table,
employee, which references the
id from the department table to make a connection between employee and departments through the
department_id column on the employee table. Provided is a subset of the employee data table.
If we wanted to see employees with their associated department we can run the following SQL query:
SELECT department.id AS department_id , department.name AS department_name , employee.first_name , employee.last_name FROM department INNER JOIN employee ON employee.department_id = department.id ;
This query would display the following result set (shortened for brevity):