Tutorial How To Use Join In Database

Swati Mishra

Content Writer
BY using Joins we can retrieve data from multiple tables. Join in an operation by which we retrieval of data from more than one table at a time. We can use the join with the SELECT statement, in which the SELECT list contains the information about the name of columns which we have retrieved from the table. The From clause define the name of tables from which data are combined and then retrieve. And WHERE clause define the rows which include in result by using join operator.


Syntax


Code:
SELECT col_name,col_name[col_name]
 
FROM tab_name[CROSS/INNER/[LEFT/RIGHT]OUTER] JOIN tab_name
 
[ON tab_name.ref_col_name join_operator tab_name.ref_col_name]
 
[WHERE search_condition]
From where col_name specifies the name of the column, which can be one or more than one table displayed. Where tab_name is the table name where data retrieval is possible here. Where ref_col_name is the col_name in which tables are combine using common keys. Join_operator is to join the table by using operator.


For join the two table common keys are sharing between them they are combine the both table. For using the join primary key validated against the foreign key.

Due to duplicate column name where ambiguity is possible in multiple tables, table alias is required there. A table alias is keyword which is uniquely identifying the table. A table alias defined in the FROM clause of the SELECT statement.

Syntax for table alias

Code:
FROM tab_name table_alais
Where tab_name is table name which is combining in the SQL query, and the table_alias is the keyword. Table_alias are follow the rules of identifier.

Now we will define the different types of JOIN describing below.
  • Inner Join: By using inner join we can display data from multiple tables. In the inner join data are display after comparing the values which is present in column. In the inner join those rows will display where those values will satisfy the condition in common column. Rows in both tables will not display when they will not satisfy the condition.


Syntax for inner join


Code:
SELECT col_name, col_name [, col_name]
 
FROM tab_name JOIN tab_name
 
ON tab_name.ref_col_namejoin_operator tab_name.ref_col_name

  • Outer Join: By using outer join when the result will display all rows from one table and matching rows from another table. If the table have not matching record then the outer join display NULL for the column of the related table.

Syntax for outer join

Code:
SELECT col_name, col_name [, col_name]
 
FROM tab_name [LEFT/RIGHT] OUTER JOIN tab_name
 
ON tab_name.ref_col_name join_operator tab_name.ref_col_name

Where LEFT OUTER JOIN consider all rows from the first table and matching rows from the second table whereas RIGHT OUTER JOIN consider all rows from second table and matching rows from the first table.

  • Cross Join: By using CROSS keyword a join that includes multiple table that is called Cross join. The output of cross join in the Cartesian product. Cross join will possible in between each row of first table and each row of second table. Cartesian product means number of rows in the first table multiplied by number of rows in the second table. It means cross join between from row to row.

Syntax of cross join

Code:
SELECT *
 
FROM tab_name CROSS JOIN tab_name

Where * is used for select all record and CROSS JOIN is keyword and tab_name is table name.

  • Equi Join: Equi join is used for equality where two or more table is compared for equality.
Equi join display the redundant column data by using asterisk (*) sign in the select list. And equal sign (=) is comparison operator which is used for comparison.

Syntax of Equi join

Code:
SELECT *
 
FROM tab_name 1, tab_name2
 
WHERE tab_name1.col_name=tab_name2.col_name

  • Natural Join: By using natural join result will display without redundant column data. Natural join is the type of equi join. In the natural join associated table have unique column name, it may be one or more.


Syntax of natural join

Code:
SELECT *
 
FROM tab_name1
 
JOIN tab_name2
 

steve taylor

White Belt
Nice tutorial, Before this tutorial I know all these concept for only how to perform join operation with the help of student and employee table.Here we learn these concept by programming method and I know how to retrieve data by using SQL commands from database table.
 

VIVEK KUMAR

White Belt
Well description of join operation .Join clause in the SQL used to join two or more table logically to retrieve some set of data from database table. you describe all join operation in your tutorial which is very useful to learning basics join operations in the SQL.
 

Manish Mishra

Content Writer
The use of select command gives this excellent feature a wing to use join that is able to access and retrieve the database upon request. It can have access to the multiple table at a time and by using select command we can perform this operation quite easily.
 
Top