When data from more than one table in the database is required, a join condition is used. Rows in one table can be joined to rows in another table according to common values that exist in the corresponding columns (usually primary and foreign key columns).
To display data from two or more related tables, write a simple join condition in the WHERE clause.
Example:
Use a join to query data from more than one table:
SELECT table1.column, table2.column FROM table1, table2 WHERE table1.column1 = table2.column2;
Prefix the column name with the table name when the same column name appears in more than one table.
In the syntax:
- table1.column – Denotes the table and column from which data is retrieved.
- table1.column1 = – Is the condition that joins (or relates) the tables together.
- table2.column2.
Guidelines
- When writing a SELECT statement that joins tables, precede the column name with the table name for clarity and to enhance 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. For example, to join four tables, a minimum of three joins is required. This rule may not apply if your table has a concatenated primary key, in which case more than one column is required to uniquely identify each row.
Types of Joins
To join tables, you can use Oracle’s join syntax. Below is the list of types of joins available.
- Natural join
- Equijoin
- Nonequijoin
- Outer join
- Self-join
- Cross join
Qualifying Ambiguous Column Names
When joining two or more tables, you need to qualify the names of the columns with the table name to avoid ambiguity. Without the table prefixes, the DEPARTMENT_ID column in the SELECT list could be from either the DEPARTMENTS table or the EMPLOYEES table. Therefore, it is necessary to add the table prefix to execute your query. If there are no common column names between the two tables, there is no need to qualify the columns. However, using a table prefix improves performance, because you tell the Oracle server exactly where to find the columns.
Qualifying column names with table names can be very time consuming, particularly if table names are lengthy. Therefore, you can use table aliases, 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, thereby using less memory.
The table name is specified in full, followed by a space, and then the table alias. For example, the EMPLOYEES table can be given an alias of e, and the DEPARTMENTS table an alias of d.
Guidelines
- Table aliases can be up to 30 characters in length, but shorter aliases are better than longer ones.
- If a table alias is used for a particular table name in the FROM clause, that table alias must be substituted for the table name throughout the SELECT statement.
- Table aliases should be meaningful.
- A table alias is valid only for the current SELECT statement.
Natural Join
You can join tables automatically based on the columns in the two tables that have matching data types and names. You do this by using the NATURAL JOIN keywords. The NATURAL JOIN clause is based on all the columns in the two tables that have the same name. It selects rows from tables that have the same names and data values of columns.
Example:
SELECT country_id, location_id, country_name,city FROM countries NATURAL JOIN locations;
In the example shown above, the COUNTRIES table is joined to the LOCATIONS table by the COUNTRY_ID column, which is the only column of the same name in both tables. If other common columns were present, the join would have used them all.
Equijoins
An equijoin is a join with a join condition containing an equality operator. An equijoin combines rows that have equivalent values for the specified columns. To determine an employee’s department name, you compare the values in the DEPARTMENT_ID column in the EMPLOYEES table with the DEPARTMENT_ID values in the DEPARTMENTS table. The relationship between the EMPLOYEES and DEPARTMENTS tables is an equijoin; that is, values in the DEPARTMENT_ID column in both tables must be equal. Often, this type of join involves primary and foreign key complements.
Retrieving Records with Equijoins
Consider the example shown below:
SELECT e.employee_id, e.last_name, e.department_id,
d.department_id, d.location_id
FROM employees e JOIN departments d
ON e.department_id = d.department_id;
In the example above:
– The SELECT clause specifies the column names to retrieve:
- Employee last name, employee ID, and department ID, which are columns in the EMPLOYEES table.
- Department ID and location ID, which are columns in the DEPARTMENTS table.
– The FROM clause specifies the two tables that the database must access:
- EMPLOYEES table
- DEPARTMENTS table
– The WHERE clause specifies how the tables are to be joined:
e.department_id = d.department_id
Because the DEPARTMENT_ID column is common to both tables, it must be prefixed with the table alias to avoid ambiguity. Other columns that are not present in both the tables need not be qualified by a table alias, but it is recommended for better performance.
Additional Search Conditions Using the AND and WHERE Operators
In addition to the join, you may have criteria for your WHERE clause to restrict the rows in consideration for one or more tables in the join.
The example shown below, performs a join on the DEPARTMENTS and LOCATIONS tables and, in addition, displays only those departments with ID equal to 20 or 50. To add additional conditions to the ON clause, you can add AND clauses. Alternatively, you can use a WHERE clause to apply additional conditions.
Both queries produce the same output.
SELECT d.department_id, d.department_name, l.city FROM departments d JOIN locations l ON d.location_id = l.location_id AND d.department_id IN (20, 50);
SELECT d.department_id, d.department_name, l.city FROM departments d JOIN locations l ON d.location_id = l.location_id WHERE d.department_id IN (20, 50);
Retrieving Records with Nonequijoins
The example shown below, creates a nonequijoin to evaluate an employee’s salary grade. The salary must be between any pair of the low and high salary ranges.
SELECT e.last_name, e.salary, j.grade_level
FROM employees e JOIN job_grades j
ON e.salary
BETWEEN j.lowest_sal AND j.highest_sal;
It is important to note that all employees appear exactly once when this query is executed. No employee is repeated in the list. There are two reasons for this:
- None of the rows in the job grade table contain grades that overlap. That is, the salary value for an employee can lie only between the low salary and high salary values of one of the rows in the salary grade table.
- All of the employees’ salaries lie within the limits that are provided by the job grade table. That is, no employee earns less than the lowest value contained in the LOWEST_SAL column or more than the highest value contained in the HIGHEST_SAL column.
Note: Other conditions (such as =) can be used, but BETWEEN is the simplest. Remember to specify the low value first and the high value last when using the BETWEEN condition. The Oracle server translates the BETWEEN condition to a pair of AND conditions.Therefore, using BETWEEN has no performance benefits, but should be used only for logical simplicity. Table aliases have been specified in the example shown above for performance reasons, not because of possible ambiguity.
Retrieving Records by Using the USING Clause
You can use the USING clause to match only one column when more than one column matches. You cannot specify this clause with a NATURAL join. Do not qualify the column name with a table name or table alias.
Example:
SELECT country_id, country_name, location_id, city FROM countries JOIN locations USING (country_id) ;
In the example shown above, the COUNTRY_ID columns in the COUNTRIES and LOCATIONS tables are joined and thus the LOCATION_ID of the location where an employee works is shown.
Retrieving Records by Using the ON Clause
The join condition for the natural join is basically an equijoin of all columns with the same name. Use the ON clause to specify a join condition. With this, you can specify join conditions separate from any search or filter conditions in the WHERE clause. The ON clause makes code easy to understand.
Example:
SELECT e.employee_id, e.last_name, j.department_id, FROM employees e JOIN job_history j ON (e.employee_id = j.employee_id);
In this example, the EMPLOYEE_ID columns in the EMPLOYEES and JOB_HISTORY tables are joined using the ON clause. Wherever an employee ID in the EMPLOYEES table equals an employee ID in the JOB_HISTORY table, the row is returned. The table alias is necessary to qualify the matching column names.
You can also use the ON clause to join columns that have different names. The parentheses around the joined columns, as in the example shown above, (e.employee_id = j.employee_id), is optional. So, even ON e.employee_id = j.employee_id will work.
Left Outer Join
A join between two tables that returns all matched rows, as well as the unmatched rows from the left table is called a LEFT OUTER JOIN.
Example:
SELECT c.country_id, c.country_name, l.location_id, l.city FROM countries c LEFT OUTER JOIN locations l ON (c.country_id = l.country_id) ;
This query retrieves all the rows in the COUNTRIES table, which is the left table, even if there is no match in the LOCATIONS table.
Right Outer Join
A join between two tables that returns all matched rows, as well as the unmatched rows from the right table is called a RIGHT OUTER JOIN.
Example:
SELECT d.department_id, d.department_name, l.location_id, l.city FROM departments d RIGHT OUTER JOIN locations l ON (d.location_id = l.location_id) ;
This query retrieves all the rows in the LOCATIONS table, which is the right table, even if there is no match in the COUNTRIES table.
Full Outer Join
A join between two tables that returns all matched rows, as well as the unmatched rows from both tables is called a FULL OUTER JOIN.
Example:
SELECT e.last_name, d.department_id, d.manager_id, d.department_name FROM employees e FULL OUTER JOIN departments d ON (e.manager_id = d.manager_id) ;
This query retrieves all the rows in the EMPLOYEES table, even if there is no match in the DEPARTMENTS table. It also retrieves all the rows in the DEPARTMENTS table, even if there is no match in the EMPLOYEES table.
Self-Join: Example
Consider the example shown below:
SELECT worker.last_name || ' works for '
|| manager.last_name
FROM employees worker JOIN employees manager
ON worker.manager_id = manager.employee_id
ORDER BY worker.last_name;
Sometimes you need to join a table to itself. To find the name of each employee’s manager, you need to join the EMPLOYEES table to itself, or perform a self-join. The example shown above joins the EMPLOYEES table to itself. To simulate two tables in the FROM clause, there are two aliases, namely worker and manager, for the same table, EMPLOYEES.
In this example, the WHERE clause contains the join that means “where a worker’s manager ID matches the employee ID for the manager.”
Cross Join
A CROSS JOIN is a JOIN operation that produces the Cartesian product of two tables.
Example:
SELECT department_name, city FROM department CROSS JOIN location;
The CROSS JOIN syntax specifies the cross product. It is also known as a Cartesian product.A cross join produces the cross product of two relations, and is essentially the same as the comma-delimited Oracle Database notation.
You do not specify any WHERE condition between the two tables in the CROSS JOIN.