JOINS in SQL

Introduction of JOINS in SQL

Using a relational database, you can store data in several logical tables that are all linked together. To get a comprehensive and useful collection of data, you must combine these tables and query data from each one. Joins in SQL Server can take a variety of forms. Data from one table is used to identify rows in another table using the join type. This article will teach you about the various “SQL Server joins” available for combining data from many tables.

Describe JOINS in SQL

It is possible to extract data from many tables by using SQL JOINS. A SQL JOIN operation is carried out anytime a SQL query contains references to two or more tables.

There are four distinct kinds of joins that can be performed with SQL:

• INNER JOIN in SQL (Referred to as a SIMPLE JOIN)

• LEFT OUTER JOIN in SQL (Referred to as a LEFT JOIN)

• RIGHT OUTER JOIN in SQL. (Referred to as a RIGHT JOIN)

• FULL L OUTER JOIN (Referred to as a FULL JOIN)

Thus, let’s talk about the syntax of SQL JOINS, have a look at several graphical representations of SQL JOINS, and investigate some cases.

INNER JOIN In SQL

As long as the requirement is met, the INNER JOIN keyword will select every row from each of the tables. This keyword will combine all rows from both tables where the criteria are met, i.e. the related field’s value will be the same, to produce the resultant set.

Syntax

The syntax for the SQL INNER JOIN is written as follows:

SELECT columns

FROM table A

INNER JOIN table B

ON table A.column = table B.column;

Table A = first table A

Table B = Second table B

Visual Representation

The entries where table A and table B intersect will be returned using an INNER JOIN in SQL

Example

Let’s examine a query that demonstrates the use of the INNER JOIN.

SELECT students.register_number, subjects.language, students.age

FROM students

INNER JOIN language

ON students.register_number = subjects.register_number

SUBJECT BY students.registration_number;

If there is a matching register number entry in both the students and subjects tables, this example would retrieve all rows from both tables.

Since they don’t exist in either table, the entries in the student’s table with register numbers entries 04 and 06 would be excluded. Since the register number 10 and 11 does not exist in the student’s table, the record from the subjects table would be removed.

LEFT OUTER JOIN IN SQL

This join retrieves all of the rows from the table on the join’s left side and matches rows from the table on the join’s right side. The result-set will include null for any rows that don’t have a matching row on the right. Another name for LEFT JOIN is LEFT OUTER JOIN.

Syntax:

SELECT table A.column1,table1.column2,table2.column1,….

FROM table A

LEFT JOIN table B

ON table A.matching_column = table B.matching_column;

Table A: First table A.

Table B: Second table B

matching_column: Column that is common to both the table A and table B.

Visual Representation

Example For (LEFT OUTER JOIN):

SELECT Student.NAME, Subject.Language

FROM Student

LEFT JOIN Subject

ON Subject.Register_number = Student.Register_number;

RIGHT OUTER JOIN IN SQL

Complementary to LEFT JOIN is RIGHT JOIN. This join gives back all the rows from the table on the right side of the join as well as any matching rows from the table on the left. The result-set will include null for any rows for which there is no corresponding row on the left.

Syntax

SELECT table A.column1,table A.column2,tableB.column1,….

FROM table A

RIGHT JOIN table B

ON table A.matching_column = table B.matching_column;

Table A: First table A.

Table B: Second table B

matching_column: Column that is in common to both the table A and Table B.

Visual Illustration

Example of (RIGHT JOIN)

SELECT Student.NAME, Subject. Language

FROM Student

RIGHT JOIN Subject

ON Subject.Register_number = Student.Register_number;

FULL JOIN IN SQL

By integrating the results of both LEFT JOIN and RIGHT JOIN, FULL JOIN produces the result-set. The rows from both table A and table B are all included in the result set. The result-set will include NULL values for the rows where there was no match.

Syntax

SELECT table A.column1,table A.column2,table B.column1,….

FROM table A

FULL JOIN table B

ON table A.matching_column = table B.matching_column;

Table A: First table A.

Table B: Second table B

matching_column: Column that is in common to both the table A and table B.

Visual Representation

Example For FULL JOIN

SELECT Student.NAME, Subject.Language

FROM Student

FULL JOIN Subject

ON Subject.Register_number = Student.Register_number;

Conclusion

Thus the JOINS in SQL and their different types are discussed extensively in this blog with precise examples to assist you in understanding the concept in depth. Hope you enjoyed learning about JOINS in SQL.