Joins - how they work

A join is an operation that defines a relationship between two or more tables to determine which records are retrieved or acted upon. What that means is that a join is conditional — similar to a WHERE clause or criteria expression — in that the join specifies which records (rows) are selected in both tables. The type of join significantly impacts which records are retrieved or acted upon.

Scribe's Database Retrieval component supports a number of different types of join operation. The graphical interface provided by Scribe in the Join tab of Database Retrieval Inspector offers the following types of join:

The best way to explain the working of Joins is by example, based on two tables: Customers and Orders.

Inner join

Returns all rows from the left table (Customers) that can be joined with rows in the right table (Orders). Rows in Customers that do not have counterparts in Orders are not included in the result.

Example:

select CustomerName, OrderID, Amount from Customers join Orders on Customers.CustomerID = Orders.CustomerID

Left Outer join

Returns all rows from the left table (Customers), joined with rows in the right table (Orders). Rows in Customers that do not have counterparts in Orders are included in the result with NULLs for the fields in Orders table.

Example:

select CustomerName, OrderID, Amount from Customers left join Orders on Customers.CustomerID = Orders.CustomerID

Right Outer join

Returns all rows from the right table (Orders), joined with rows in the left table (Customers). Rows in B that do not have counterparts in Customers are included in the result with NULLs for the fields in Customers table.

Example:

select CustomerName, OrderID, Amount from Customers right join Orders on Customers.CustomerID = Orders.CustomerID

Full Outer join

A combination of the left and right outer joins. Any row in one table that does not have a matching key in the other table is joined with a row of NULLs.

Example:

select CustomerName, OrderID, Amount from Customers full join Orders on Customers.CustomerID = Orders.CustomerID

Cross join

The Cross join returns what's known as a Cartesian product. This means that the join combines every row from the left table with every row in the right table. The total number of the returned rows is a product of the numbers of rows in two tables.

Example:

select CustomerName, OrderID, Amount from Customers cross join Orders