SQL Joins

SQL Joins are used to retrieve information from multiple tables. A Join condition is a part of the SQL query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE clause of SELECT, UPDATE OR DELETE statements.

Types of Joins

The Oracle Database supports two different kinds of joins:

• Oracle Proprietary Joins
• SQL: 1999 Compliant Joins

The Oracle Database offers join Syntax that is SQL: 1999 compliant. Prior to the Oracle 9i release, the join syntax was different from the ANSI standards. The New SQL: 1999 compliant join syntax does not offer any performance benefits over the Oracle Proprietary joins. Below figure shows the complete hierarchy of different kinds of joins supported by the Oracle Database.

Types of Joins

Oracle Proprietary Joins

Sometimes it is required to retrieve information from multiple tables; at that time Join condition is required. Rows in one table can be joined to rows in another table according to common values existing in corresponding columns, that is, usually, Primary Key and Foreign Key.

Syntax

The Syntax for joining two tables is:

General Syntax of Oracle Proprietary Joins

Guidelines

• When Writing a SELECT statement that joins tables, precede the column name with the table name for clarify and to enhance the database access.
• If the same column name appears in more than one table, the column name must be prefixed with the table name.
• To join N tables together, you need a minimum of N-1 join conditions.

Types of Oracle Proprietary Joins

• Inner Join
• Outer Join
• Self Join

SQL: 1999 Compliant Joins

Using the SQL:1999 compliant joins, you can obtain same result as an Oracle Proprietary Joins.

Syntax

The Basic syntax of SQL:1999 Compliant joins is as below:

General Syntax of SQL:1999 Compliant Joins

Types of SQL:1999 compliant Joins

• Cross Join
• Natural Join
• Using Clause
• Full or two sided outer Join

Table Alias

Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. You can use Table Alias instead of table names. Just as a column alias gives a column another name, a table alias gives a table another name. Table aliases help to keep SQL code smaller, therefore using less memory.

Guidelines

• Table alias can be up to 30 characters in length, but shorter is better.
• If a table alias is used for a particular table name in the FROM clause, then that table alias must be substituted for the table name throughout the SELECT statement.
• Table aliases should be meaningful.
• The table alias is valid only for the current SELECT statement.

Note: The table name is specified in full, followed by a space and then the table alias.