SQL JOINS

A SQL JOIN combines two or more tables to retrieve data from multiple tables. The WHERE clause is a required element of a SQL JOINS statement when joining tables. The tables being joined are listed in the FROM clause. The SQL join is performed in the WHERE clause. Several operators can be used to join tables, such as =, <, >, <>, <=, >=, !=, BETWEEN, LIKE, and NOT. However, the most common operator is the equal (=) symbol.
Joins are the first operation(s) of the SELECT command. They produce the initial information to be filtered and processed by the remaining parts of the statement. The result of a SQL JOIN is a composite relation (or table). It is the relation that is provided as the input or starting point for all subsequent (filtering) operations in the SELECT command.

Table Aliases:

Table alias is most often used to save keystrokes, which results in the SQL statement being shorter and easier to read. In addition, fewer keystrokes mean fewer keystroke errors. Also, programming errors are typically less frequent if you can refer to an alias, which is often shorter in length and more descriptive of the data with which you are working. Giving tables aliases also means that the columns being selected must be qualified with the table alias.

Example:

The following is an example of table aliases used in a query to get employee name and his/her department name from employee and department table:

SELECT e.full_name, d.dept_name FROM employee e, department d WHERE E.dept_no = d.dept_no;

Output:

The following type of joins can be created using SQL:

• EQUI JOINS / INNER JOINS
• NATURAL JOINS
• NON-EQUI JOINS
• OUTER JOINS
  • LEFT OUTER JOIN
  • RIGHT OUTER JOIN
  • FULL OUTER JOIN
• CROSS JOINS / CARTESIAN PRODUCT
• SELF JOINS

SQL EQUI JOIN /SQL INNER JOIN :

An SQL inner join is where two tables are joined by a relationship between two columns in the tables. An SQL inner join uses SET operation in relational algebra, called an intersection. An intersection of two sets produces a set containing elements that exist in both sets.

Example:
The following is an example of SQL inner join. It is same query mentioned above:

SELECT e.full_name, d.dept_name FROM employee e, department d WHERE E.dept_no = d.dept_no;

Output:

Here only matching column values will be returned and rest of data will be ignored.

SQL NATURAL JOIN:
A natural join joins two tables by their common column names. The natural join automatically detects the common column names in A and B and links them together. In practice, it is a good idea to avoid natural joins in your applications if you can. The process of adding to or removing a column from a table can drastically change the results of a natural join query.

Example:
The following query returns all matching row from employee and department table.

SELECT full_name, dept_name FROM employee natural join department

Output:

Note: No where clause is used hence no column names are mentioned for comparison. Here, the natural join automatically detects the common column names in Employee and Department (in this case just column dept_no) and links them together. Also the number of rows is the number of rows in department table which is lesser.

SQL NON-EQUI JOINS:
Although joins based on equality between matching columns (equi-joins) are by far the most common joins, SQL also allows you to join tables based on other comparison operators.

Example:

The following SQL statement returns employee name and department names that do not have a corresponding record in both tables.

SELECT e.full_name, d.dept_name FROM employee e, department d WHERE E.dept_no! = d.dept_no;

SQL OUTER JOINS:

An OUTER JOIN is used to return all rows that exist in one table, even though corresponding rows do not exist in the joined table. The (+) symbol is used to denote an OUTER JOIN in a query. The (+) is placed at the end of the table name in the WHERE clause. The table with the (+) should be the table that does not have matching rows.

Example:
The following join condition will return all employee with their department name and department name in which no employee works:

SELECT e.full_name, d.dept_name FROM employee e, department d WHERE E.dept_no(+) = d.dept_no;

Output:

The OUTER JOIN can be broken down into following joins:

SQL LEFT OUTER JOIN: Outer join on left side table, reverse of above example.
SQL RIGHT OUTER JOIN: Outer join on right side table, same as above example.
SQL FULL OUTER JOIN: Outer join on both tables.

An outer join selects all of the rows of an inner join plus some rows outside of the relationship.

CROSS JOINS in SQL/ CARTESIAN PRODUCT:
A cross join in SQL also known as Cartesian product is the most basic join, as there’s no WHERE clause to join the tables. Instead, all the rows from all the tables listed in the join are included in the results set. The number of rows in result table is multiplication of number of rows in joining tables.

Example:
The example uses keyword ‘CROSS JOIN’ in SQL to join two tables:

SELECT full_name, dept_name
FROM employee CROSS JOIN department
ORDER BY full_name;

Output:

Example 2:
The following example does not use any keyword, just simple SQL statement to retrieve data:

SELECT full_name, dept_name
FROM employee, department
ORDER BY full_name;

Output will be same as query above.

SQL SELF-JOINS:
Some queries involve a relationship that a table has with itself.

Example:
Suppose you want to list the names of all employees and their managers. Each employee appears as a row in the Employee table, and the manager column contains the employee number of the employee’s manager. It would appear that the manager column should be a foreign key for the table that holds data about managers. In fact it is a foreign key for the Employee table itself. Here is what this query looks like:

SELECT e.full_name, m.full_name
FROM employee e, employee m
WHERE e.mgr_id = m.emp_id;

Output:

Note: This query won’t return employee name who are not working under any manager or who are at top level.

Example 2:
This is same query as above but will return all employees regardless of their manager’s existence:

SELECT e.name, m.name
FROM employee e, employee m
WHERE e.mgr_id = m.emp_id(+);