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

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

Self Join

Self Join:

Let’s explain the need for a self join with an example,

 Employee Table:


 

I want to retrieve employee’s data for country India.

We can retrieve data using classic sub query use but this is not efficient solution.

SELECT * FROM [Employee] WHERE COUNTRY IN (SELECT Country FROM [Employee] WHERE EmployeeName='Nagnath')

What if I want to check manager name for employee?

Answer: Efficient solution is self join   

Definition: A self join is basically when a table is joined to itself. The way you should visualize a self join for a given table is by imagining that a join is performed between two identical copies of that table. And that is exactly why it is called a self join – because of the fact that it’s just the same table being joined to another copy of itself rather than being joined with a different table.

Note: for self join use aliases otherwise the column names would be ambiguous.

Self join example:

As with any join there must be condition upon which self join is performed (i.e. inner join, outer join)

SELECT e1.*
FROM employee e1, employee e2
WHERE e1.Country = e2.Country
AND e2.EmployeeName='Nagnath';

 


 

 

Find managers for employee we can do this by self join:

SELECT e1.EmployeeName,e1.Country,e1.PrimaryLanguage,e1.Gender ,e2.EmployeeName as Manager
FROM Employee e1
INNER JOIN Employee e2
ON e1.ManagerID = e2.ID



 

 

Exists predicate with sub query

Exists predicate

T-SQL supports predicate like EXISTS with sub query, Exists accept sub query as input returns true if any rows exist in sub query else return false.


Syntax:

WHERE [NOT] EXISTS (subquery)

 

Example:


SELECT * FROM  Products P WHERE
 EXISTS (SELECT * FROM Categories  as C) 

 


 

 

You can negate EXISTS predicate with not logical operator, NOT EXISTS returns TRUE if zero rows are returnedBelow example returns  

 

SELECT * FROM  Products P WHERE  
NOT EXISTS (SELECT * FROM Categories  asWHERE CategoryName IN ('Mobile','books')  and  P.Categoryid=C.Categoryid) 

 

 


 

You can use multiple columns sub query in EXISTS predicate but with IN predicate this is not possible, SQL server will return error message

 

SELECT * FROM  Products P WHERE   P.Categoryid
 NOT IN (SELECT * FROM Categories  asWHERE CategoryName IN ('Mobile','books')  and  P.Categoryid=C.Categoryid)

 


 

 

What happens to null value with EXISTS?

 

If sub query returns null value, exists return true, I was surprised with result but its fact.

Exists statement resolves null value with True

 
SELECT * FROM  Products P WHERE
 EXISTS (SELECT NULL)