SQL JOINS

“An SQL Query walks into a bar and joins two tables. “LOL

I know it’s not that simple, but this blog helps you to understand the basics of SQL join. Here I am explaining the four important joins along with examples and the syntax used for each Joins.

In SQL, when we need to fetch details which are scattered across multiple tables then the only solution is to bring all the tables together and this is where Joins are used. Based on the data we need; joins are categorized into four types. They are Inner join, Left join, Right join and Full Join.

• INNER JOIN (JOIN)

In simple words, Inner Join returns rows that have matching values in both tables.

Syntax: -

SELECT column_name(s)

FROM table1

INNER JOIN table 2 ON table1.column_name=table2.column_name;

Example: - fig (1)

SQL Query: -

SELECT * FROM Country_table c

INNER JOIN States_table s

ON c.Country_id = s.COUNTRY_ID

Output: -

Note: - As you can see, only the matching rows of both tables are displayed.

• LEFT JOIN

Left Join returns all rows from the left table (Country_table) and the corresponding rows from the right table (States_table). If there is no matching row, NULLS are returned as values from the second table.

Syntax: -

SELECT column_name(s)

FROM table1

LEFT JOIN table2 ON table1.column_name=table2.column_name.

SQL Query: -

SELECT * FROM Country_table c

LEFT JOIN States_table s

ON c.Country_id = s.Country_id

Output: = Ref fig (1)

Note: - As you can see VEGAS from States_table has been excluded because it doesn’t have any matching and all the states from the Country_table have been included.

• RIGHT JOIN

Returns all rows from the right table (States_table) with corresponding rows from the left table (Country_table). If there are no matching rows, Nulls are returned as values from the left table.

Syntax: -

SELECT column_name(s) FROM table1

RIGHT JOIN table2 ON table1.column_name=table2.column_name;

Example: - fig (2)

SQL Query –

SELECT * FROM Country_table c RIGHT JOIN States_table s

ON c.Country_id = s.Country_id

Output: -

Note: - Here you can see it pulls all the values from the right table (States_table) even if there are no values for VEGAS then also it pulls and keep it as NULL and pulls only the matching values from the left table (Customer_table)

• FULL JOIN

Returns all rows from both tables irrespective of whether they will match or not. If there is no matching row in the second table, NULLS are returned.

Syntax: -

SELECT column_name(s)

FROM table1

FULL JOIN table2 ON table1.column_name=table2.column_name;

Example: - fig (3)

SQL Query: -

SELECT * FROM Country_table c

FULL JOIN States_table s

ON c.Country_id = s.Country_id

Output: -

I would like to mention one more join in this blog those are CROSS JOINS. These joins are of less relevance but used occasionally. As it will bring all possible combinations it will add more columns to the dataset and add workload to the dataset.

• CROSS JOIN

Returns all possible combinations of rows from both tables.

Syntax: -

SELECT column_name(s)

FROM table 1

CROSS JOIN table 2

Conclusion: -

JOINS play a vital role in SQL because they allow us to combine data from multiple tables, which is necessary for complex queries. Without JOINS we need to rely on subqueries, or multiple queries to retrieve data which can be inefficient and cumbersome. Using Joins helps to reduce the workload placed on the database.

Thanks for reading!

Aiswarya Rajendrakumar(aishwaryarajendrakumar01@gmail.com)