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

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



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