SQL Join – Inner, Left, Right & Full Joins

1. Joins in SQL

In this SQL (Structured Query language) tutorial, we will see SQL Join. In this SQL Join article, we will discuss SQL Inner Join, SQL Left Join, SQL Right join, SQL Full Joins. Moreover, we will also see join query in SQL and SQL Join example. Along with this, we will discuss cartesian Join and Self Join in SQL.
So, let’s start SQL Join.

SQL Join

SQL Join – Inner, Left, Right & Full Joins

2. What is SQL Join?

An SQL be a part of clause combines columns from one or additional tables during a relational database. It creates a set which will be saved as a table or used because it is. A SQL join could be a means that for combining columns from one (self-join) or additional tables by using values common to every. ANSI-standard SQL specifies 5 forms of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS. As a special case, a table (base table, view, or joined table) will join to itself during a self-join.
Do you know about SQL Data Types

3. Inner, Left, Right and Full Joins in SQL

A SQL be a part the of statement is used to mix data or rows from 2 or a lot of tables supported a common field between them. Differing kinds of Joins are:

  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL JOIN

Consider the two SQL Join tables below:

SQL Join

Sample of SQL Join

Student

SQL Join

SQL Join – Student Course

StudentCourse

a. SQL INNER Join

The INNER JOIN in SQL keyword selects all rows from each the tables as long because the condition satisfies, it can be used to create the result-set by combining all rows from each the tables wherever the condition satisfies i.e. the value of the common field are going to be same.
Have a look at SQL Operators

SQL Join

SQL Inner Join

The syntax for Inner Join:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER join table2
ON table1.matching_column = table2.matching_column;

table1: first table.
table2: Second table
matching_column: Column common to each the tables.
Example Join Queries(INNER JOIN)

SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER join StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;

Output:

SQL Join

SQL Join – Output

b. SQL LEFT Join

This SQL Left Join returns all the rows of the table on the left side of the be a part of and matching rows for the table on the right aspect of the Join. The rows that there’s no matching row on right aspect, the result-set can contain null. LEFT be a part of is also known as a LEFT OUTER join.
Let’s revise SQL RDBMS Database System
The syntax for SQL Left Join:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT be a part of table2
ON table1.matching_column = table2.matching_column;

table1: first table.
table2: Second table
matching_column: Column common to both the tables.
Note: we are able to also use LEFT OUTER join rather than LEFT join, each are same.

SQL Join

SQL Join – SQL Left Join

Example Join Queries(LEFT JOIN):

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT be a part of StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output:

SQL Join

Join Query in SQL Left Join

c. SQL RIGHT Join

RIGHT join in SQL is similar to LEFT JOIN. This join returns all the rows of the table on the proper aspect of the join and matching rows for the table on the left aspect of being a part of. The rows that there’s no matching row on left aspect, the result-set can contain null. RIGHT join is additionally referred to as RIGHT OUTER join.
The syntax of Right Join:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT join table2
ON table1.matching_column = table2.matching_column;

table1: first table.
table2: Second table
matching_column: Column common to each of the tables.
Do you know the term SQL Expressions
Note: we are able to also use RIGHT OUTER be a part of instead of the RIGHT join, each are same.

SQL Joins

SQL Right Join

Example Join Queries(RIGHT JOIN):

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT join StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output of Right Join:

SQL Join

SQL Right Join Query

d. FULL Join

FULL Join creates the result-set by combining results of each LEFT join and RIGHT JOIN. The result-set can contain all the rows from each of the tables. The rows for which there’s no matching, the result-set can contain NULL values.
The syntax of Full Join:

SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL join table2
ON table1.matching_column = table2.matching_column;

table1: first table.
table2: Second table
matching_column: Column common to each the tables.
Let’s revise the SQL Create Database
Example Join Queries(FULL JOIN):

SQL Join

SQL Full Join

SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL be a part of StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;

Output of SQL Full Joins:

SQL Join

SQL Full Join

4. Cartesian Join & Self Join

Consider the two SQL Join tables below:

SQL Join

Sample 2 – SQL Join

StudentCourse

SQL Join

Self SQL Join

The Cartesian Join in SQL is also called CROSS JOIN. In a Cartesian Join, there’s a join for every row of one table to each row of another table. This usually happens once the matching column or where the condition isn’t specified.
In the absence of a wherever condition, the Cartesian Join can behave like a Cartesian join. i.e., the number of rows in the result-set is that the product of the number of rows of the 2 tables.
In the presence of where condition this be a part of can perform like an INNER be a part of.
Generally speaking, Cross join is comparable to an inner be a part of where the join-condition can always evaluate to True
The syntax of Cartesian Join:

SELECT table1.column1 , table1.column2, table2.column1...
FROM table1
CROSS join table2;

table1: first table.
table2: Second table
Example Join Queries(CARTESIAN JOIN):
In the below query we’ll choose NAME and Age from Student table and COURSE_ID from StudentCourse table. within the output, you’ll see each} row of the table Student is joined with every row of the table StudentCourse. the total rows within the result-set = 4 * 4 = 16.

SELECT Student.NAME, Student.AGE, StudentCourse.COURSE_ID
FROM Student
CROSS join StudentCourse;

Output:

SQL Join

Example of Cartesian Join

SELF JOIN: Because the name signifies, in SELF be a part of a table is joined to itself. That is, every row of the table is joined with itself and every one different rows counting on some conditions. In different words, we will say that it’s a be a part of between 2 copies of the identical table.
Syntax of Self Join:

SELECT a.coulmn1 , b.column2
FROM table_name a, table_name b
WHERE some_condition;

table_name: Name of the table.
some_condition: Condition for selecting the rows.
Example Queries(SELF JOIN):

SELECT a.ROLL_NO , b.NAME
FROM Student a, Student b
WHERE a.ROLL_NO < b.ROLL_NO;

Output:

SQL Join

Queries of Self Join

So, this was all in SQL Join. Hope you like our explanation.

5. Conclusion

In this SQL Join tutorial, we learned about the various joins that are available with SQL. Moreover, we discussed inner SQL Join, Left SQL Join, Right SQL Join, Full SQL Join. At last, we discussed Cartesian Join and Self Join in SQL. Still, if any doubt, ask in the comment tab.
For reference

1 Response

  1. Rudrakshi says:

    To display easy words to understand

Leave a Reply

Your email address will not be published. Required fields are marked *

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.