What is the Difference between join and union ?



What is the Difference between join and union ?

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 - Union All

    Union - Union All

  • 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

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

Join

Syntax

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


Related Searches to What is the Difference between join and union ?