StatusNeo

A complete guide to SQL from scratch- Part-III (Joins)

Introduction

In our previous blogs, we have covered the basics of SQL(Structured Query language), DDL (Data Definition Language), and DML(Data Manipulation Language).

And also discuss how to implement restrictions and modifications to the table structure.

Link to part 1 of this series- https://statusneo.com/sql/

Link to partt2 of this series- https://statusneo.com/sql-constraints/

In this blog, we are going to cover the different types of joins.

What are SQL Joins?

SQL joins are the operations performed to fetch the data from two or more tables.

Different types of SQL Joins are-

• Inner Join (Also known as simple join)
• Cross Join
• Natural Join
• Left Outer Join (Also known as left join)
• Right Outer Join (Also known as right join)
• Full Outer Join (Also known as full join)

Let’s discuss each one of them.

Inner Join (Simple Join)

Inner Join operation returns the matching values in both the table, that is the intersection of the table on the applied condition.

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

Cross Join

Cross Join operation returns the cartesian product of the two tables. It doesn’t let us specify the join clause, but we can use the where clause in it. It combines each row of one table to the other.

Select * from table1
 CROSS JOIN table2;

Natural Join

The Natural Join operation returns the rows from the two tables based on the common columns. Common columns are the ones having the same name in both the table and they must be of the same data type. Every row from both tables will appear in this case. The difference between natural join and inner join is that in natural join the common column will appear only once in the output while in inner join it will appear twice.

Select *
FROM table1
NATURAL JOIN table2;

Left Outer Join

The Left Join operation returns all the rows from the LEFT-side table specified in the ON condition and only those rows from the other table where the joined fields are equal.

SELECT columns
FROM table1
LEFT [OUTER] JOIN table2
ON table1.column = table2.column;

Right Outer Join

The Right Join operation returns all the rows from the Right-side table specified in the ON condition and only those rows from the other table where the joined fields are equal.

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

Full Outer Join

The Full Join operation returns all rows from the LEFT-hand table and RIGHT-hand table with nulls in a place where the join condition is not met.

SELECT columns
FROM table1
FULL [OUTER] JOIN table2
ON table1.column = table2.column;

Conclusion

That’s it from this blog, in our next blog we are going to cover the grouping and aggregation of data.

Still Curious? Visit my website to know more!

For more interesting Blogs Visit- Utkarsh Shukla Author