SQL JOİN

SQL JOİNS

SQL JOIN

As the name shows, JOIN means to combine something. In case of SQL, JOIN means “to combine two or more tables”.

The SQL JOIN clause takes records from two or more tables in a database and combines it together.

ANSI standard SQL defines five types of JOIN :

  1. inner join,
  2. left outer join,
  3. right outer join,
  4. full outer join, and
  5. cross join.

In the process of joining, rows of both tables are combined in a single table.

Why SQL JOIN is used?

If you want to access more than one table through a select statement.

If you want to combine two or more table then SQL JOIN statement is used .it combines rows of that tables in one table and one can retrieve the information by a SELECT statement.

The joining of two or more tables is based on common field between them.

SQL INNER JOIN also known as simple join is the most common type of join.

How to use SQL join or SQL Inner Join?

Let an example to deploy SQL JOIN process:

1.Staff table

IDStaff_NAMEStaff_AGESTAFF_ADDRESSMonthley_Package
1ARYAN22MUMBAI18000
2SUSHIL32DELHI20000
3MONTY25MOHALI22000
4AMIT20ALLAHABAD12000

2.Payment table

Payment_IDDATEStaff_IDAMOUNT
10130/12/200913000.00
10222/02/201032500.00
10323/02/201043500.00

So if you follow this JOIN statement to join these two tables ?

  1. SELECT Staff_ID, Staff_NAME, Staff_AGE, AMOUNT
  2.    FROM STAFF s, PAYMENT p
  3.    WHERE s.ID =p.STAFF_ID;

This will produce the result like this:

STAFF_IDNAMEStaff_AGEAMOUNT
3MONTY252500
1ARYAN223000
4AMIT253500
1ARYAN223000

SQL OUTER JOIN

In the SQL outer JOIN all the content of the both tables are integrated together either they are matched or not.

If you take an example of employee table

Outer join of two types:

1.Left outer join (also known as left join): this join returns all the rows from left table combine with the matching rows of the right table. If you get no matching in the right table it returns NULL values.

2.Right outer join (also known as right join): this join returns all the rows from right table are combined with the matching rows of left table .If you get no column matching in the left table .it returns null value.

This diagram shows the different type of joins:

SQL LEFT JOIN

The SQL left join returns all the values from the left table and it also includes matching values from right table, if there are no matching join value it returns NULL.

BASIC SYNTAX FOR LEFT JOIN:

SELECT table1.column1, table2.column2….

FROM table1

LEFTJOIN table2

ON table1.column_field = table2.column_field;

let us take two tables in this example to elaborate all the things:

CUSTOMER TABLE:

IDNAMEAGESALARY
1ARYAN5156000
2AROHI2125000
3VINEET2431000
4AJEET2332000
5RAVI2342000

This is second table

ORDER TABLE:

O_IDDATECUSTOMER_IDAMOUNT
00120-01-201223000
00212-02-201222000
00322-03-201234000
00411-04-201245000

join these two tables with LEFT JOIN:

SQL SELECT ID, NAME, AMOUNT,DATE

FROM CUSTOMER

LEFT JOIN ORDER

ON CUSTOMER.ID = ORDER.CUSTOMER_ID;

This will produce the following result:

IDNAMEAMOUNTDATE
1ARYANNULLNULL
2AROHI300020-01-2012
2AROHI200012-02-2012
3VINEET400022-03-2012
4AJEET500011-04-2012
5RAVINULLNULL

SQL RIGHT JOIN

The SQL right join returns all the values from the rows of right table. It also includes the matched values from left table but if there is no matching in both tables, it returns NULL.

Basic syntax for right join:

SELECT table1.column1, table2.column2…..

FROM table1

RIGHT JOIN table2

ON table1.column_field = table2.column_field;

let us take an example with 2 tables table1 is CUSTOMERS table and table2 is ORDERS table.

CUSTOMER TABLE:

IDNAMEAGESALARY
1ARYAN5156000
2AROHI2125000
3VINEET2431000
4AJEET2332000
5RAVI2342000

and this is the second table:

ORDER TABLE:

DATEO_IDCUSTOMER_IDAMOUNT
20-01-201200123000
12-02-201200222000
22-03-201200334000
11-04-201200445000

Here we will join these two tables with SQL RIGHT JOIN:

SQL> SELECT ID,NAME,AMOUNT,DATE

FROM CUSTOMER

RIGHT JOIN ORDER

ON CUSTOMER.ID = ORDER.CUSTOMER_ID;

IDNAMEAMOUNTDATE
2AROHI300020-01-2012
2AROHI200012-02-2012
3VINEET400022-03-2012
4AJEET500011-04-2012

SQL FULL JOIN

The SQL full join is the result of combination of both left and right outer join and the join tables have all the records from both tables. It puts NULL on the place of matches not found.

SQL full outer join and SQL join are same. generally it is known as SQL FULL JOIN.

SQL full outer join:

What is SQL full outer join?

SQL full outer join is used to combine the result of both left and right outer join and returns all rows (don’t care its matched or unmatched) from the both participating tables.

Syntax for full outer join:

SELECT *

FROM table1

FULL OUTER JOIN table2

ON table1.column_name = table2.column_name;

Note:here table1 and table2 are the name of the tables participating in joining and column_name is the column of the participating tables.

Let us take two tables to demonstrate full outer join:

table_A

AM
1m
2n
4o

table_B

AN
2p
3q
5r

Resulting table

AMAN
2n2p
1m
4o
3q
5r

Because this is a full outer join so all rows (both matching and non-matching) from both tables are included in the output. Here only one row of output displays values in all columns because there is only one match between table_A and table_B.

Pictorial representation of full outer join:

SQL Cross Join

When each row of first table is combined with each row from the second table, known as Cartesian join or cross join. In general words we can say that SQL CROSS JOIN returns the Cartesian product of the sets of rows from the joined table.

We can specify a CROSS JOIN in two ways:

Using the JOIN syntax.

the table in the FROM clause without using a WHERE clause.

SYNTAX of SQL Cross Join

SELECT * FROM [TABLE1] CROSS JOIN [TABLE2]

OR

SELECT * FROM [ TABLE1] , [TABLE2]

Let us take an example of two tables,

Table1 – MatchScore

PlayerDepartment_idGoals
Franklin12
Alan13
Priyanka22
Rajesh35

Table2 – Departments

Department_idDepartment_name
1IT
2HR
3Marketing

SQL Statement:

  1. SELECT * FROM MatchScore CROSS JOIN Departments

After executing this query , you will find the following result:

PlayerDepartment_idGoalsDepatment_idDepartment_name
Franklin121IT
Alan131IT
Priyanka221IT
Rajesh351IT
Franklin122HR
Alan132HR
Priyanka222HR
Rajesh352HR
Franklin123Marketing
Alan133Marketing
Priyanka223Marketing
Rajesh353Marketing