Joins

 A join is a query that combines rows from two or more tables, views, or materialized views.

The following example joins the employees and departments tables (FROM clause), selects only rows that meet specified criteria (WHERE clause), and uses projection to retrieve data from two columns (SELECT). Sample output follows the SQL statement.

qa testing training courses malaysia

SELECT email, department_name
FROM   employees 
JOIN   departments
ON     employees.department_id = departments.department_id
WHERE  employee_id IN (100,103)
ORDER BY email;

EMAIL                     DEPARTMENT_NAME
------------------------- ------------------------------
AHUNOLD                   IT
SKING                     Executive

The following graphic represents the operations of projection and selection in the join shown in the preceding query.

software development training courses malaysia

Figure 7-1 Projection and Selection

Description of "Figure 7-1 Projection and Selection"

Most joins have at least one join condition, either in the FROM clause or in the WHERE clause, that compares two columns, each from a different table. The database combines pairs of rows, each containing one row from each table, for which the join condition evaluates to TRUE. The optimizer determines the order in which the database joins tables based on the join conditions, indexes, and any available statistics for the tables.

it security training courses malaysia

Join types include the following:

• Inner joins

  An inner join is a join of two or more tables that returns only rows that satisfy the join condition. For example, if the join condition is employees.department_id=departments.department_id, then rows that do not satisfy this condition are not returned.

• Outer joins

  An outer join returns all rows that satisfy the join condition and also returns rows from one table for which no rows from the other table satisfy the condition.

  The result of a left outer join for table A and B always contains all records of the left table A, even if the join condition does not match a record in the right table B. If no matching row from B exists, then B columns contain nulls for rows that have no match in B. For example, if not all employees are in departments, then a left outer join of employees (left table) and departments (right table) retrieves all rows in employees even if no rows in departments satisfy the join condition (employees.department_id is null).

  The result of a right outer join for table A and B contains all records of the right table B, even if the join condition does not match a row in the left table A. If no matching row from A exists, then A columns contain nulls for rows that have no match in A. For example, if not all departments have employees, a right outer join of employees (left table) and departments (right table) retrieves all rows in departments even if no rows in employees satisfy the join condition.

  A full outer join is the combination of a left outer join and a right outer join.

• Cartesian products

  If two tables in a join query have no join condition, then the database performs a Cartesian join. Each row of one table combines with each row of the other. For example, if employees has 107 rows and departments has 27, then the Cartesian product contains 107*27 rows. A Cartesian product is rarely useful.

networking training courses malaysia