AppDividend
Latest Code Tutorials

SQL JOINS Tutorial For Beginners | SQL JOINS Example

0

SQL JOINS Tutorial For Beginners | SQL JOINS Example is today’s topic. SQL is the special-purpose programming language designed for managing information in the relational database management system (RDBMS). The word relational is key; it specifies that the DBMS is organized in such a way that there are clear relations defined between the different sets of data. SQL joins are used to combine the records from two or more tables in a database. SQL JOIN clause is used to combine the rows from two or more tables, based on a related column between them.

SQL JOINS Tutorial For Beginners

Different types of joins are:

  1. INNER JOIN
  2. LEFT JOIN
  3. RIGHT JOIN
  4. FULL JOIN
  5. CROSS JOIN
  6. SELF JOIN

#INNER JOINS in SQL

SQL Inner Join is used to select all the rows from tables for the match between the columns in tables.

SQL INNER JOIN is based on the concept of EQUI JOINS.

EQUI JOINS are those who use the comparison operator (=) for combining records from two or more tables.

When the condition is satisfied, column values for each matched pair of rows of two tables are combined into a result row.

 

SQL JOINS Tutorial For Beginners

The shaded part above Shows a common records between both the tables.

SYNTAX

Select columns from Table_1 
INNER JOIN Table_2 on Table_1.column = Table_2.column;

So, in the above statements columns represent the column names of the tables.

Table_1 and Table_2 are the names of tables.

And the condition i.e Table_1.column = Table_2.column is used to compare the columns which are common in both the tables.

Table_1: Employee

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
201 Shivam Jalandhar Punjab 20000
301 Karan Allahabad Uttar Pradesh 40000
401 Suraj Kolkata West Bengal 60000
501 Akash Vizag Andhra Pradesh 70000

 

Table_2: Department

Dept_no Emp_id
123 301
214 401
125 505

 

See the following query.

Select Department.Dept_no, Employee.Emp_name, Employee.City, Employee.Salary from Department 
INNER JOIN Employee on Department.Emp_id = Employee.Emp_id;

 

INNER JOINS in SQL

#Left Join in SQL

Left outer join returns all rows in the table which is on the left side matched with the rows of a table in right side. This gives the conclusion that the SQL left Join always contains the rows in the left table.

 

Left Join in SQL

The above Venn diagram shows that the left table rows will always be displayed whether the conditions match or not.

SYNTAX

Select column_1, column_2… 
from table_1 LEFT JOIN table_2 ON CONDITION;

See the following tables.

CUSTOMER:

ID NAME AGE CITY
1 Rohit 20 Patna
2 Shivam 18 Jalandhar
3 Pranav 19 Dharamshala

 

ORDERS:

O_ID Cust_ID City AMOUNT
201 1 Patna 3000
202 2 Jalandhar 4000
203 4 Kolkata 1000

 

Let’s clear this with an example.

Select Orders.O_ID, Customer.id, customer.name, Orders.amount 
From CUSTOMER 
LEFT JOIN Orders ON Customer.ID = Orders.Cust_ID;

See the following output.

 

SQL Left Join

As you can see, all the contents of a left table are displayed, whether it is matched with a right table or not. Right table contents which are matched with the left table is displayed as well and which are not matched is displayed with NULL values.

#RIGHT JOIN in SQL

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

 

RIGHT JOIN in SQL

The above Venn diagram illustrates that all the rows of the right table will be displayed whether the condition matches or not.

SYNTAX

Select column_1, column_2… 
from table_1 RIGHT JOIN table_2 ON CONDITION;
CUSTOMER:
ID NAME AGE CITY
1 Rohit 20 Patna
2 Shivam 18 Jalandhar
3 Pranav 19 Dharamshala

 

ORDERS:
O_ID Cust_ID City AMOUNT
201 1 Patna 3000
202 2 Jalandhar 4000
203 4 Kolkata 1000

 

Query

Select Orders.O_ID,Customer.id,customer.name,Orders.amount 
From CUSTOMER 
RIGHT JOIN Orders ON Customer.ID = Orders.Cust_ID;
OUTPUT

 

SQL RIGHT JOIN

EXPLANATION

As you can see, all the contents of the right table are displayed, whether it is matched with a left table or not. Left table contents which are matched with a right table is displayed as well and which are not matched is displayed with the NULL values.

#FULL JOIN in SQL

SQL full join returns all the rows in the left table, right table and matching rows in both the tables or you can say it is a combination of left and right join. It is also known as a full outer join.

 

FULL JOIN in SQL

The above Venn diagram illustrates that all the rows of both the table will be displayed whether the conditions match or not.

SYNTAX
Select * from table1 
FULL OUTER JOIN table2 ON CONDITION;

Let’s consider a table.

CUSTOMER:
ID NAME AGE CITY
1 Rohit 20 Patna
2 Shivam 18 Jalandhar
3 Pranav 19 Dharamshala

 

ORDERS:
O_ID Cust_ID City AMOUNT
201 1 Patna 3000
202 2 Jalandhar 4000
203 4 Kolkata 1000

 

Let’s clear this with an example.

Select * from Customer 
FULL OUTER JOIN Orders ON Customer.ID = Orders.cust_id;
OUTPUT

The above Statement will not work in MySQL, Because the SQL full outer join returns the result set that is combined results of both SQL left join and SQL right join. So to generate the result, we have to use the UNION operator.

Statement
Select * from Customer 
LEFT JOIN Orders ON Customer.id = Orders.Cust_ID 
UNION 
Select * from Customer 
RIGHT JOIN Orders ON Customer.id = Orders.Cust_id;

See the output.

 

SQL FULL JOIN

#CROSS JOIN in SQL

SQL Cross Join is used to join the table having no condition in which all the records of the first table comes with all the records of the second table.

This type of Join is also called a Cartesian product.

Unlike the INNER JOIN or LEFT JOIN, the cross join does not establish a relationship between the joined tables.

NOTE:

If Where Condition is not used with CROSS JOIN, then it will behave like a cartesian product.

 

CROSS JOIN in SQL

Here arrows are pointing to the rows of a table.

SYNTAX

SELECT COLUMNS_NAME FROM TABLE_1 
CROSS JOIN TABLE_2;

Suppose there are two tables.

SQL CROSS JOIN

 

QUERY
Select * from STUDENT 
CROSS JOIN COURSE;
OUTPUT

SQL CROSS JOIN Tutorial

 

All the combinations of rows are listed.

#SELF JOIN in SQL

In the self join table is joined itself, i.e. each row is joined with itself and all other rows depending on the conditions.

 

SELF JOIN in SQL

Syntax

SELECT a.column1, b.column2 
from table_name a, table_name b 
where condition;

Table: (Employee)

Emp_id Emp_name City State Salary
101 Rohit Patna Bihar 30000
201 Shivam Jalandhar Punjab 20000
301 Karan Allahabad Uttar Pradesh 40000
401 Suraj Kolkata West Bengal 60000
501 Akash Vizag Andhra Pradesh 70000

 

QUERY

Select a.emp_name,b.salary 
from employee a,employee b 
where a.salary < b.salary;
OUTPUT

 

SQL Self Join

Finally, SQL JOINS Tutorial For Beginners | SQL JOINS Example is over.

Leave A Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.