Union

    • The union operator combines the results of two or more queries into a single result set. But no.of columns must match in both/all the queries (and also the order) which are used for union.
    • We can union two tables irrespective of common field.
    • The UNION set operator is used for combining data from two tables which have columns with the same datatype.
      • Union – Returns with no duplicate rows.
      • Union all – Retruns with duplicate rows (No. of rows returned = No. of rows in Query1 + No. of rows in Query 2).
Union unionall except intersect
  • We cannot use the union operator within a create view statement.
  • We cannot use the union operator on text and image columns.
Union in SQL

Syntax

SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions]
UNION
SELECT expression1, expression2, ... expression_n
FROM tables
[WHERE conditions];

Join

  • Joins are used to extract information from more than one table based on the related column/coloums (PK and RFK) any no. of rows can be retrived based on matching colums.
  • We can join two tables by ‘join’ if they have common field.
  • A Join is used for displaying columns with the same or different names from different tables.
  • Different types of joins are Inner join=equi join=join , outer join (Right outer join/right join, Left outer join / left join), Cross join , and full outer join/outer join.
  • We can use join operator within a create view statement.
  • We can use the join operator on text and image columns.
Cross Join

Syntax

SELECT column-names
FROM table-name1 JOIN table-name2 
ON column-name1 = column-name2
WHERE condition

Categorized in:

SQL

Tagged in:

, , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , , ,