Codechef4u is a community for computer professionals,by computer professionals,just like you; who loves sharing and helping each others,Join them
Share your post

Left outer Join or Left join

Products Table



Categories Table



Left outer Join or Left join:

 The LEFT JOIN keyword returns all rows from the left table (Products), with the matching rows in the right table (Categories). The result is NULL in the right side when there is no match.



Syntax:

SELECT
column_name(s)
FROM Table1 (Left Side Table)
LEFT JOIN Table2 (Right Side Table)
ON table1.column_name=table2.column_name
or:
SELECT column_name(s)
FROM Table1 (Left Side Table)
LEFT OUTER JOIN Table2 (Right Side Table)
ON Table1.column_name=Table2.column_name

Example:

SELECT
ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P
LEFT OUTER JOIN Categories C ON P.CategoryID=C.CategoryID


Left outer query will return below result 



Cross join

Cross join:

A cross joins that produces Cartesian product of the tables that are involved in the join. The size of a Cartesian product is the number of the rows in the first table multiplied by the number of rows in the second table like this. The size of a Cartesian product result set is the number of rows in the first table multiplied by the number of rows in the second table.

Syntax:

SELECT
column_name(s)
FROM Table1 (Left Side Table)
CROSS JOIN Table2 (Right Side Table);

 

Example:

SELECT ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P
CROSS JOIN Categories C

Cross join t-sql query will return result (Products table 7 rows * Categories table 6 rows =42 rows)


You can write a query like this also:

--Below query also returns same data
SELECT ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
 FROM Products P , Categories C 

Equi Join and natural join

Equi Join

Equi join is a special type of join in which we use only equality operator. Hence, when you make a query for join using equality operator then that join query comes under Equi join.

Example:

SELECT
ProductName AS [Product Name]
,UnitPrice AS [Price]
,CategoryName AS [Product Category],ImagePath AS [Image]
FROM Products P
JOIN Categories C ON P.CategoryID=C.CategoryID
--Note:Using Clause is not supported by SQL Server



Note
Inner join can have equality (=) and other operators (like <,>,<>) in the join condition.

  • Equi join only have equality (=) operator in the join condition.
  • Equi join can be an Inner join, Left Outer join, Right Outer join
  • The USING clause is not supported by SQL Server .

 

 Natural Join

Natural join is a type of equi join which occurs implicitly by comparing all the same names columns in both tables. The join result have only one column for each pair of equally named columns.

Natural Join Example

SELECT * FROM Products
NATURAL JOIN Categories
Note : not supported by sql server ,error message from sql server  “Incorrect syntax near ‘Categories’.