JOINS And UNION in Ms SQL

UNION and UNION ALL are SQL operators used to concatenate 2 or more result sets. This allows us to write multiple SELECT statements, retrieve the desired results, then combine them together into a final, unified set.

UNION, will allow you to join multiple datasets into one dataset and will remove any duplicates that exist.

SYNTAX —

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;

UNION ALL, allows you to join multiple datasets into one dataset, but it does not remove any duplicate rows.

SYNTAX —

SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;

we can retrieve data from multiple tables using the Joins — there are mainly 5 types of joins —

1.INNER JOIN

2.LEFT JOIN

3.RIGHT JOIN

4.FULL OUTER JOIN

5.CROSS JOIN

To start with INNER JOIN, it shows only the matching data from both the tables.

SYNTAX —

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

FIG.1 INNER JOIN

2. The LEFT JOIN will include the data from and left table as well as the matching data of both tables.

SYNTAX —

SELECT column_names FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

FIG.2 LEFT JOIN

3. The RIGHT JOIN will include the data from the Right-hand Table and also the matching rows of the both table.

SYNTAX —

SELECT columns FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

FIG.3 RIGHT JOIN

4. The FULL OUTER JOIN returns all rows from the LEFT-hand table and RIGHT-hand table, including NULLS if the JOIN condition is not met.

SYNTAX —

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name = table2.column_name
WHERE condition;

FIG.4 FULL OUTER JOIN

5. The CROSS JOIN basically returns record from both tables without any conditions, every row of one table gets combined with every row of another table.

SYNTAX —

SELECT column_name(s)
FROM table1
CROSS JOIN table2;

FIG.5 EXAMPLE OF CROSS JOIN.

That’s it for this Article , I hope you liked it. I would really appreciate if you would share this Article and Drop your Thoughts, Suggestions and Recommendations, always looking to Improve, Learn and Grow.

Until Next Time,

STAY SHARP. STAY CURIOUS.

CIAO