FROM CLAUSE


DESCRIPTION

The SQL Server (Transact-SQL) FROM clause is used to list the tables and any joins required for the query in SQL Server.

SYNTAX

The syntax for the FROM clause in SQL Server (Transact-SQL) is:

FROM table1
[ { INNER JOIN
  | LEFT OUTER JOIN
  | RIGHT OUTER JOIN
  | FULL OUTER JOIN } table2
ON table1.column1 = table2.column1 ]

Parameters or Arguments

table1 and table2
The tables used in the SQL statement. The two tables are joined based on table1.column1 = table2.column1.

Note:

  • There must be at least one table listed in the FROM clause in SQL Server (Transact-SQL).
  • If there are two or more tables listed in the FROM clause, these tables are generally joined in the FROM clause using INNER or OUTER joins. Although the tables can also be joined using the old syntax in the WHERE clause, we recommend using new standards and including your join information in the FROM clause. 

EXAMPLE - WITH ONE TABLE

It is difficult to explain the syntax for the SQL Server FROM clause, so let's look at some examples.

We'll start by looking at how to use the FROM clause with only a single table.

For example:

SELECT *
FROM employees
WHERE first_name = 'Jane';

In this SQL Server FROM clause example, we've used the FROM clause to list the table called employees. There are no joins performed since we are only using one table.

EXAMPLE - TWO TABLES WITH INNER JOIN

Let's look at how to use the FROM clause with two tables and an INNER JOIN.

For example:

SELECT suppliers.supplier_id, suppliers.supplier_name, orders.order_date
FROM suppliers 
INNER JOIN orders
ON suppliers.supplier_id = orders.supplier_id;

This SQL Server FROM clause example uses the FROM clause to list two tables - suppliers and orders. And we are using the FROM clause to specify an INNER JOIN between the suppliers and orders tables based on the supplier_id column in both tables.

EXAMPLE - TWO TABLES WITH OUTER JOIN

Let's look at how to use the FROM clause when we join two tables together using an OUT JOIN. In this case, we will look at the LEFT OUTER JOIN.

For example:

SELECT employees.employee_id, contacts.last_name
FROM employees
LEFT OUTER JOIN contacts
ON employees.employee_id = contacts.contact_id
WHERE employees.first_name = 'Sarah';

This SQL Server FROM clause example uses the FROM clause to list two tables - employees and contacts. And we are using the FROM clause to specify a LEFT OUTER JOIN between the employees and contacts tables based on the employee_id column in both tables.

 

Source: www.techonthenet.com


Advertisements