Database Operations 2 Concepts

Database Operations (Joins)

An SQL join clause (join operations in relational algebra), combines columns from one or more tables in a relational database. It creates a set tha can be saved as a table or used as it is.

Joins Example

There are 5 types of join:

Inner
Left Outer
Right Outer
Full Outer
Cross

These are the tables that we will use for the examples:

Orders
OrderID	CustomerID	ItemID
10022	12	10
12356	45	2
19583	45	43
15473	8	4

Items
ItemID	Colour	Size
1	Red	Large
2	Blue	Medium
3	Yellow	Small
4	Purple	Large

Inner Join

The Inner Join keyword selects records that have matching values in both tables.

The Inner Join keyword selectors all rows from both tables as long as there is a match between the columns. If there are records in table1 that do not have matches in table2, these orders will not be shown.

                        
                            SELECT Orders.OrderID,
Items.Colour
FROM Orders
INNER JOIN Items ON
Orders.ItemID = 
Items.ItemID;
                            
                        
                    

The result will output a table with just the OrderID and the Colours columns from the Orders and Items tables respectively.

Left (Outer) Join

The Left Outer Join performs a join starting with the first (left-most) table and then any matching second (right-most) table records.

                        
                            SELECT Orders.CustomerID, 
Items.Size
FROM Items LEFT JOIN Orders
ON Items.Item = Orders.ItemID;

This outputs a table with all the CustomerID's and their corresponding sizes of the items they've ordered. If they have no sizes ordered the field will show as blank.

Right (Outer) Join

The Right Join joins two tables and fetches rows based on a conditio, which is matching in both the tables (before and after the JOIN clause), and the unmatched rows will be also available from the table written after the JOIN clause.

This syntax is similar to the left join but is instead on the right table.

                        
                            SELECT Orders.OrderID,
Items.Size
FROM Items RIGHT JOIN ORDERS
ON Items.ItemID = Orders.ItemID;

This outputs a table with all the OrderID's and the Sizes of the Items, the sizes might not all display however.

Full (Outer) Join

The Full Outer Join returns a result set that includes rows from both left and right tables. When no matching rows exist for the row in the left table, the columns of the right table will have nulls. Similarly, when no matching rows exist for the row in the right table, the column of the left table will have nulls.

                        
                            SELECT Orders.CustomerID,
Items.Colour
FROM Orders
FULL OUTER JOIN Items
ON Orders.ItemID = Items.ItemID;

This will have the CustomerID and the Item Colours in a table of its own with nulls on both sides.

Cross Join

A Cross Join is used when you wish to create combination of every row from two tables. All row combinations are included in the result, this is commonly called cross product join. A common use for a cross join is to create and obtain all combinations of items, such as colours and sizes.

This is also known as a Cartesian Product.

                        
                            SELECT Orders.ItemID,
Items.Colour
FROM Orders
CROSS JOIN Items;

The result is a big table which is multiplied by the amount of records in the Orders table multiplied by the amount of records in the Items table, so at least 12.

Sources: (click on them to see them)

DB Joins Concepts