SQL Tutorials

(here is an example from the hr schema)

SELECT l.location_id, city, department_name FROM locations l, departments WHERE l.location_id = departments.location_id;

SQL Cross Join:

It is without the WHERE clause

Example 1:

Select * from locations, departments;

Example 2:

Select * from locations CROSS JOIN departments;

SQL Natural Join:

Natural Join is based on all columns with same name in both tables. You should not qualify the column names with the table name or table alias name.

Example 1:

SELECT location_id, city, department_name FROM locations NATURAL JOIN departments; we cannot say :: SELECT l.location_id, city, department_name FROM locations l NATURAL JOIN departments; (even though the LOCATION_ID column is in both tables, we did not qualify this column in the SELECT clause. You cannot qualify the column names when using the NATURAL JOIN clause.)

Example 2: ( For 3 Tables)

SELECT field 1, field 2, field 3 from table1 NATURAL JOIN table2 NATURAL JOIN table3;

Traditional Oracle syntax for above query is:

Select field1, field2, field3 from table1, table2, table3 where table1_id = table2_id and table2_id = table3_id;

"USING" in SQL Joins:

If you have many columns that have the same names in the tables you are joining and those columns do not have the same datatype, or you want to specify the columns should be considered for an equijoin, you can use the JOIN … USING syntax. The USING clause specifies the column names that should be used to join the tables. The column names should not be qualified with a table name or table alias.

Example:

SELECT location_id, city, department_name from locations JOIN departments USING (location_id);

"ON" condition in SQL Joins

When you do not have common column names between tables to make a join or if you want to specify arbitrary join conditions, you may use the JOIN ON syntax. SELECT * from table1 JOIN table2 ON table1.id = table2.id ;

SQL OUTER Joins:

When you join two tables, the first table may have rows that do not match any rows in second table and vice versa. If you perform inner join on those tables, all unmatched rows are excluded from the result. OUTER JOINS don’t exclude unmatched rows.

There are three types of outer joins:

Left Outer Join

Right Outer Join

Full Outer Join

1. Left Outer Join:

A left outer join is a join between two tables that returns rows based on the matching condition, as well as unmatched rows from the table to the left of the JOIN clause. SELECT * FROM firsttable LEFT JOIN secondtable ON firsttable.keyfield = secondtable.foreign_keyfield

2. Right Outer Join:

A right outer join is a join between two tables that returns rows based on the matching condition, as well as unmatched rows from the table to the right of the JOIN clause. SELECT * FROM firsttable RIGHT JOIN secondtable ON firsttable.keyfield = secondtable.foreign_keyfield

3. Full Outer Join:

The full outer join combines the features of the left outer join and the right outer join. It retains the unmatched rows from both the left and the right tables.

Little more on SQL Joins:

In Oracle8i Right outer join is like: select last_name, department_name from employees , departments where employees.department_id(+) = departments.department_id;

In Oracle9i Right outer join is like: select last_name,department_name from employees right outer join departments on employees.department_id = departments.department_id;

Full Outer Join select last_name, department_name from employees full outer join departments on employees.department_id = departments.department_id;