SQL Tutorial

SQL Join:
Before doing anything we need to create Table. Use the following code to create Customers table in management studio.

CREATE TABLE [TestDataBase].[dbo].[Customers]
(
customer_id int,
first_name varchar(255),
last_name varchar(255),
email varchar(255),
address varchar(255),
city varchar(255),
PRIMARY KEY (customer_id)
);

Note: TestDataBase should exists before executing the above command.
Now fill the data by using management studio or something else.

customer_id first_name last_name email address city
1 Pekka Kosonen pekka.kosonen@microsoft.com Tampere Tampere
2 Mika Murinen mika.muurinen Helsinki Helsinki
3 Vesa Kauppanen vesa.kauppanen Salo Salo
4 Kari Salo kari.salo@gmail.com Espoo Espoo
5 Petri Ylitalo petri.ylitalo@nokia.com Espoo Salo

Create a Order table with foreign keys relation

CREATE TABLE TestDataBase.[dbo].[Orders]
(
order_id int,
order_date varchar(255),
amount varchar(255),
PRIMARY KEY (order_id),
Customers_id int FOREIGN KEY REFERENCES Customers(customer_id)
);

Add some related data to Order tables

order_id order_date amount Customers_id
1 5th July, 2016 €200 4
2 4th August, 2016 €100 3
3 1st May, 2015 €40 3
4 2nd May, 2015 €10 2
5 2nd July, 2013 €300 4
6 3rd June, 2015 €100 2
7 5th May, 2013 €300 1

Inner join:
Do a basic search where customer id = 3 by the following sql statement

select order_date, amount
from [TestDataBase].[dbo].[Customers]
join [TestDataBase].[dbo].[Orders]
on [TestDataBase].[dbo].[Customers].customer_id = [TestDataBase].[dbo].[Orders].order_id
where customer_id = 3

Above statement will generates the following results:

order_date amount
1st May, 2015 €40

If we remove the where constraints we get the following results:

order_date amount
5th July, 2016 €200
4th August, 2016 €100
1st May, 2015 €40
2nd May, 2015 €10
2nd July, 2013 €300

If we switch the from and join we get the same result as above as shown by the following sql statements.

select order_date, amount
from [TestDataBase].[dbo].[Orders]
join [TestDataBase].[dbo].[Customers]
on [TestDataBase].[dbo].[Customers].customer_id = [TestDataBase].[dbo].[Orders].order_id

And the query result is same as above. The reason it is inner join that can be seen by Venn diagram. The default is inner meaning if we don't put anything inner is assumed. We can explicitly put the inner in front of join as well.

Following statements are equivalent it just declare o and c

select order_date, amount
from [TestDataBase].[dbo].[Orders] o
inner join [TestDataBase].[dbo].[Customers] c
on c.customer_id = o.order_id

Left join:
Returns a new table based on the criteria. Use the following sql statements.

select first_name, last_name, order_date, amount
from [TestDataBase].[dbo].[customers] c
left join [TestDataBase].[dbo].[orders] o
on c.customer_id = o.customer_id

The result of it are as follows:

first_name last_name order_date amount
Pekka Kosonen 5th May, 2013 €300
Mika Murinen 2nd May, 2015 €10
Mika Murinen 3rd June, 2015 €100
Vesa Kauppanen 4th August, 2016 €100
Vesa Kauppanen 1st May, 2015 €40
Kari Salo 5th July, 2016 €200
Kari Salo 2nd July, 2013 €300
Petri Ylitalo NULL NULL

Petri Ylitalo did not submit any order so the order date and the amount is NULL
This can be seen by the Venn diagram.

Right join:
Just opposite of left join.

We changed data a bit as following:

order_id order_date amount customer_id
1 5th July, 2016 €200 4
2 4th August, 2016 €100 3
3 1st May, 2015 €40 3
4 2nd May, 2015 €10 2
5 2nd July, 2013 €300 4
6 3rd June, 2015 €100 2
7 5th May, 2013 €300 NULL

Last customer if is NULL, it can be other id as well, say it does not exists in the Customer table.
If we execute the following command we get the result of right join.

select first_name, last_name, order_date, amount
from [TestDataBase].[dbo].[customers] c
right join [TestDataBase].[dbo].[orders] o
on c.customer_id = o.customer_id

The result of this are as follows.

first_name last_name order_date amount
Kari Salo 5th July, 2016 €200
Vesa Kauppanen 4th August, 2016 €100
Vesa Kauppanen 1st May, 2015 €40
Mika Murinen 2nd May, 2015 €10
Kari Salo 2nd July, 2013 €300
Mika Murinen 3rd June, 2015 €100
NULL NULL 5th May, 2013 €300

7th order was not submitted by anybody so the name field is NULL. It means if an order has been submitted but the customer is not present in the table it will be shown as NULL. This can be used to check if there is any orphan order in the system. This can be seen by Venn diagram easily.

The last join is full join. It will add both table. If some data is missing it will be NULL in the table.

select first_name, last_name, order_date, amount
from [TestDataBase].[dbo].[customers] c
full join [TestDataBase].[dbo].[orders] o
on c.customer_id = o.customer_id

The result of the above query is

Pekka Kosonen NULL NULL
Mika Murinen 2nd May, 2015 €10
Mika Murinen 3rd June, 2015 €100
Vesa Kauppanen 4th August, 2016 €100
Vesa Kauppanen 1st May, 2015 €40
Kari Salo 5th July, 2016 €200
Kari Salo 2nd July, 2013 €300
Petri Ylitalo NULL NULL
NULL NULL 5th May, 2013 €300

According to new order table, order 7 was not ordered by anybody, no customer_id in the table (we edited later from 1 to NULL). According to new data, customer 1 did not submitted any order. so his order related information was NULL. Petri Ylitalo did not submit any order and his order amount and date are NULL. On the other hand we have an order in the table without customer id (NULL or it can be any customer id) that exists in the customer table. As a result name fields are NULL but there are order date and amount. This can be easily seen by the Venn diagram.