Sql Join Understanding

Posted Date: 23 Jun 2010 |Updated: 23-Jun-2010 |Category: SQL Server |Author: Jayesh L Lolariya |Member Level: Silver |Points: 3 |

by reading this content you can come to know how join is working in sql to get output faster and safer.

SQL Joins

SQL Joins are used to relate information in different tables. A Join condition is a part of the sql query that retrieves rows from two or more tables. A SQL Join condition is used in the SQL WHERE Clause of select, update, delete statements.

The Syntax for joining two tables is:

SELECT col1, col2, col3...
FROM table_name1, table_name2
WHERE table_name1.col2 = table_name2.col1;

If a sql join condition is omitted or if it is invalid the join operation will result in a Cartesian product. The Cartesian product returns a number of rows equal to the product of all rows in all the tables being joined. For example, if the first table has 20 rows and the second table has 10 rows, the result will be 20 * 10, or 200 rows. This query takes a long time to execute.

Lets use the below two tables to explain the sql join conditions.

database table "product";

product_id product_name supplier_name unit_price
100 Camera Nikon 300
101 Television Onida 100
102 Refrigerator Vediocon 150
103 Ipod Apple 75
104 Mobile Nokia 50
database table "order_items";

order_id product_id total_units customer
5100 104 30 Infosys
5101 102 5 Satyam
5102 103 25 Wipro
5103 101 10 TCS
SQL Joins can be classified into Equi join and Non Equi join.

1) SQL Equi joins

It is a simple sql join condition which uses the equal sign as the comparison operator. Two types of equi joins are SQL Outer join and SQL Inner join.

For example: You can get the information about a customer who purchased a product and the quantity of product.

2) SQL Non equi joins

It is a sql join condition which makes use of some comparison operator other than the equal sign like >, <, >=, <=

Comments

Author: Jivani09 May 2013 Member Level: Gold Points : 3

you can below link check are information about join and types of join and join to where use to example are show
(1)inner join
(2)outer join
->left outer join
->right outer join
->full outer join
(3)cross join
(4)left outer join where null
(5)right outer join where null
(6)outer join where null

http://bharat1990.wordpress.com/2013/04/25/types-of-join-in-sql-server/

Author: Mohite Mina20 Mar 2014 Member Level: Silver Points : 10

Types of Joins

1) Inner Joins
2) Outer Joins
3) Self Join

Inner Join
The join that displays only the rows that have a match in both the joined tables is known as inner join. This is default join in the query and view Designer.
SELECT *
FROM tblProgram1 TPA
INNER JOIN tblProgram2 TPB
ON TPA.id = TPB.id
We can write our inner join query like this also it will give same result
SELECT *
FROM tblProgram1 TPA
JOIN tblProgram2 TPB
ON TPA.id = TPB.id
In inner Join we are having different types of Joins those are

1) Equi Join
2) Natural Join
3) Cross Join
Equi Join
An equi-join is used to match two columns from two tables using explicit operator =:
select *
from tblProgram1 TPA, tblProgram2 TPB
where TPA.id = TPB.id

Natural Joins
The Natural join is same as our Equi join but only the difference is it will restrict
to display redundant values.
SELECT *
FROM tblProgram1
NATURAL JOIN tblProgram2
These NATURAL Joins won't work in our SQL Server (only supports in Oracle) it will throw
syntax error.
Cross Join
A cross join that produces Cartesian product of the tables that involved in the join. The
size of a Cartesian product is the number of the rows in first table multiplied by the
number of rows in the second table.
SELECT *
FROM tblProgram1
CROSS JOIN tblProgram2
Or we can write it in another way also
select *
from tblProgram1 , tblProgram2

Outer Joins

A join that return all the rows that satisfy the condition and unmatched rows in the joined table is an Outer Join.

We are having three types of Outer Joins

Left Outer Join
Right Outer Join
Full Outer Join

Left Outer Join

The left outer join displays all the rows from the first table and matched rows from the second table.

SELECT u.id,u.Program,o.chapter
FROM tblProgram1 u
LEFT OUTER JOIN tblProgram2 o
ON u.id=o.id

Right Outer Join

The right outer join displays all the rows from the second table and matched rows from the first table.

SELECT TPA.id,TPA.Program,TPB.chapter
FROM tblProgram1 TPA
RIGHT OUTER JOIN tblProgram2 TPB
ON TPA.id = TPB.id

Full Outer Join

Full Outer Join displays all the matching and non matching rows of both the tables.

SELECT TPA.id,TPA.Program,TPB.chapter
FROM tblProgram1 TPA
FULL OUTER JOIN tblProgram2 TPB
ON TPA.id = TPB.id

Self Join

Joining the table itself called self join. Self join is used to retrieve the records having some relation or similarity with other records in the same table. Here we need to use aliases for the same table to set a self join between single table and retrieve records satisfying the condition in where clause.

select e2.EmpName,e1.EmpName as 'Manager'
from EmployeeDetails e1
INNER JOIN EmployeeDetails e2
on e1.EmpID=e2.EmpMgrID

Return to Article Index

Digital Marketing by SpiderWorks Technologies, Kochi - India. ©