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

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’.



Sub Queries

Sub Queries:

Select Query inside another query is a sub query, Select statement embedded in DML statement or nested in outer query.

You can use sub queries in SELECT, INSERT, UPDATE, DELETE whenever expressions are used.


Sub query has below three parts:

Comparison: expression and comparison operator, expression compares result with sub query.

Sub queries can be introduced with one of the comparison operators (=, < >, >, > =, <, ! >, ! <, or < =).

Example:

SELECT * FROM  Products WHERE CategoryID=(SELECT CategoryID FROM Categories WHERE CategoryName='Mobile') 

 

Expression: An expression for which the result set of the sub query is searched.

Sql statement:  A SELECT statement, following the same format and rules as any other SELECT statement. It must be enclosed in parentheses.

Example:

SELECT p.ProductName,p.UnitPrice,(SELECT categoryname FROM Categories c WHERE CategoryID=p.CategoryID) AS Category FROM Products AS


Guide lines sub queries :

1.       Sub query can not include compute or for browse clause.

2.       Must include sub query in paranthises.

3.       Subquery can include where ,group by,having clauses these are optional.

4.       Up to 32 levels sub query can be nested.

5.       You can use order by clause only when top clause included.

 

Sub query types:

1.       Self contained sub query

2.       Co-related sub query 


Self contained sub query:

Sub query is completely independent and do not require any input from outer query called self contained sub query. Self contained Sub queries can be categorized based on their return type.   

  •  Self contained scalar sub query: A self contained sub query that returns single value called self contained scalar sub query. 
    

  • Self contained multi valued query:Self contained multi-valued sub query still return a single column but it may produce multiple values for the column. 

  • Table-valued sub query:sub query returns a whole table; multiple columns, multiple rows.

Correlated Sub queries:

Sub query depends on the outer query for its values. This means that the sub query is executed repeatedly, once for each row that might be selected by the outer query.

 

  Like join outer query one or more columns matches with sub query one or more columns, and sub query returns matching records. 


  more info http://codechef4u.com/post/2015/04/09/correlated-sub-queries



Self contained multi valued sub query

Self contained multi valued sub query:

Self contained multi-valued sub query still return a single column but it may produce multiple values for the column.

This multi valued sub query used with IN Predicate and this returns multiple values according to match (true or false).

Multi-valued sub Query is valid if sub query returns one value, multiple value or no values

Example:

Multi-valued sub Query returns four records

 

SELECT * FROM  Products WHERE CategoryID

IN (SELECT CategoryID FROM Categories WHERE CategoryName IN ('Mobile','books') )  



Multi-valued sub Query returns single value

SELECT * FROM  Products WHERE CategoryID in (SELECT CategoryID FROM Categories WHERE CategoryName='shoes')

Multi value sub query returns no value or null value

SELECT * FROM  Products WHERE CategoryID in (SELECT CategoryID FROM Categories WHERE CategoryName='xyz')

With Multi value sub query you can negate IN Predicate using NOT IN like other Predicate,

Example

SELECT * FROM  Products WHERE CategoryID

NOT IN (SELECT CategoryID FROM Categories WHERE CategoryName IN ('Mobile','books') )  


What happens to null value with IN?

When comparison list contains only null value, then IN statement resolves with false




When you try Exists instead of In query will return all products, reason Exists resolve with true