SQL-JOINS

SQL JOIN:

A SQL join is a Structured Query Language (SQL) instruction to combine data from two sets of data (e.g. two tables). 

SQL is a special-purpose programming language designed for managing information in a relational database management system (RDBMS).

The word relational here is key; it specifies that the database management system is organized in such a way that there are clear relations defined between different sets of data. 

Relational Database Example:

Imagine you’re running a store and would like to record information about your customers and their orders. By using a relational database, you can save this information as two tables that represent two distinct entities: customers and orders.

Table : Customers

Table: Orders

 

Relational Model:

You’ve probably noticed that these two examples share similar information. You can see these simple relations diagrammed below.

 

Note that the orders table contains two keys: one for the order and one for the customer who placed that order. In scenarios when there are multiple keys in a table, the key that refers to the entity being described in that table is called the Primary Key (PK) and other key is called a Foreign Key (FK).

In our example, order_id is a primary key in the orders table, while customer_id is both a primary key in the customers table, and a foreign key in the orders table. Primary and foreign keys are essential to describing relations between the tables.

Example:

Let’s say we want to find all orders placed by a particular customer. We can do this by joining the customers and orders tables together using the relationship established by the customer_id key:

select OrderDate, TotalAmount

from customer

join orders

on customer.customerid = orders.customerid

where customerid = 555

  

SQL Join Types:

There are four basic types of SQL joins: inner, left, right, and full

Inner Join:

Let’s say we wanted to get a list of those customers who placed an order and the details of the order they placed. This would be a perfect fit for an inner join, since an inner join returns records at the intersection of the two tables.

SELECT customer.customerid,orders.ordersid,orders.ordedate

From customers,orders

WHERE customer.customerid=orders.orderid

Or

SELECT c.customerid, o.ordersid, o.orderdate

From customers c

INNER JOIN orders o

ON c.customerid=o.orderid

 

  

Left Outer Join:

The SQL LEFT OUTER JOIN would return the all records from table1 and only those records from table2 that intersect with table1.

The SQL LEFT JOIN returns all rows from the left table, even if there are no matches in the right table. This means that if the ON clause matches 0 (zero) records in right table, the join will still return a row in the result, but with NULL in each column from right table.

This means that a left join returns all the values from the left table, plus matched values from the right table or NULL in case of no matching join predicate.

SELECT c.customerid, o.ordersid, o.orderdate

From customers c

LEFT JOIN orders o

ON c.customerid=o.orderid

Right Outer Join:

The SQL RIGHT JOIN returns all rows from the right table, even if there are no matches in the left table. This means that if the ON clause matches 0 (zero) records in left table, the join will still return a row in the result, but with NULL in each column from left table.

This means that a right join returns all the values from the right table, plus matched values from the left table or NULL in case of no matching join predicate.

SELECT c.customerid, o.ordersid, o.orderdate

From customers c

RIGHT JOIN orders o

ON c.customerid=o.orderid

FULL OUTER JOIN:

The SQL FULL JOIN combines the results of both left and right outer joins.

The joined table will contain all records from both tables, and fill in NULLs for missing matches on either side.

SELECT c.customerid, o.ordersid, o.orderdate

From customers c

FULL OUTER JOIN orders o

ON c.customerid=o.orderid

CARTESIAN or CROSS JOINS:

The CARTESIAN JOIN or CROSS JOIN returns the Cartesian product of the sets of records from the two or more joined tables. Thus, it equates to an inner join where the join-condition always evaluates to True or where the join-condition is absent from the statement.

Syntax:

The basic syntax of CARTESIAN JOIN or CROSS JOIN is as follows:

SELECT table1.column1, table2.column2...

FROM  table1, table2 [, table3 ]

Example:

SELECT c.customerid,firstname,totalamount,orderdate

FROM customer c, orders;