Joins ,Unions and Union all -- Sql server Basics

There are different types of joins available in SQL:

Inner Join: returns rows when there is a match in both tables.

Left Outer Join: returns all rows from the left table, even if there are no matches in the right table.

Right Outer Join returns all rows from the right table, even if there are no matches in the left table.

Full Outer Join: returns rows when there is a match in one of the tables.

SELF JOIN: is used to join a table to itself as if the table were two tables, temporarily renaming at least one table in the SQL statement.

Cartesian Join: returns the Cartesian product of the sets of records from the two or more joined tables.

Union and Union All

The SQL UNION clause/operator is used to combine the results of two or more SELECT statements without returning any duplicate rows.

To use UNION, each SELECT must have the same number of columns selected, the same number of column expressions, the same data type, and have them in the same order, but they do not have to be the same length.

Syntax:

The basic syntax of UNION is as follows:

Select Column1,Column2 from table1,table2  where condition
union
Select Column1,Column2 from table1,table2

Here given condition could be any given expression based on your requirement.

UNION ALL Clause:

The UNION ALL operator is used to combine the results of two SELECT statements including duplicate rows.

The same rules that apply to UNION apply to the UNION ALL operator.

Syntax:

The basic syntax of UNION ALL is as follows:

SELECT column1 [, column2 ]

FROM table1 [, table2 ]

[WHERE condition]

UNION ALL

SELECT column1 [, column2]

FROM table1 [, table2]

[WHERE condition]

There are two other clauses (i.e., operators), which are very similar to UNION clause:

SQL INTERSECT Clause: is used to combine two SELECT statements, but returns rows only from the first SELECT statement that are identical to a row in the second SELECT statement.

SQL EXCEPT Clause : combines two SELECT statements and returns rows from the first SELECT statement that are not returned by the second SELECT statement.