Tables are essential elements within a database.Tables are formed using columns, where each column must possess a name and a data type.A database can contain one or more tables and these tables can be modeled as relational. "In this blog, I will discuss how to establish relationships between multiple tables in SQL using JOIN operations.
A join clause in the Structured Query Language (SQL) combines columns from one or more tables into a new table. The operation corresponds to a join operation in relational algebra. Informally, a join stitches two tables and puts on the same row records with matching fields. Different types of JOIN include INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN and NATURAL JOIN.
I am going to use the following tables to expain each type of JOIN.
Student Table
Student Course
1. INNER JOIN
An INNER JOIN query will return all the rows from both tables you are querying where a certain condition is met. In other words, INNER JOIN will select all rows from both tables as long as there is a match between the specified columns of each table.
Syntax:
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
INNER JOIN table2
ON table1.matching_column = table2.matching_column;
Note that we can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN. With our example of above, this query will show the names and age of students enrolled in different courses.
SELECT StudentCourse.COURSE_ID, Student.NAME, Student.AGE FROM Student
INNER JOIN StudentCourse
ON Student.ROLL_NO = StudentCourse.ROLL_NO;
Output
2. LEFT JOIN
A LEFT JOIN query returns all rows from the left, or first, table, regardless of whether or not they met the JOIN condition. The query will also return the matched data from the right, or second, table.
Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
LEFT JOIN table2
ON table1.matching_column = table2.matching_column;
We can also use LEFT OUTER JOIN instead of LEFT JOIN, both are the same. In our example the query will become:
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
LEFT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output
3.RIGHT JOIN
RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows for which there is no matching row on the left side, the result-set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
RIGHT JOIN table2
ON table1.matching_column = table2.matching_column;
We can also use RIGHT OUTER JOIN instead of RIGHT JOIN, both are the same. In our example the code will be:
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
RIGHT JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output
4. FULL JOIN
FULL JOIN creates the result-set by combining results of both LEFT JOIN and RIGHT JOIN. The result-set will contain all the rows from both tables. For the rows for which there is no matching, the result-set will contain NULL values.
Syntax
SELECT table1.column1,table1.column2,table2.column1,....
FROM table1
FULL JOIN table2
ON table1.matching_column = table2.matching_column;
In our example the queries will be:
SELECT Student.NAME,StudentCourse.COURSE_ID
FROM Student
FULL JOIN StudentCourse
ON StudentCourse.ROLL_NO = Student.ROLL_NO;
Output
5. NATURAL JOIN
A natural join returns all rows by matching values in common columns having same name and data type of columns and that column should be present in both tables. Both table must have at list one common column with same column name and same data type.
The final table will accommodate all the attributes of both the tables and doesn’t duplicate the column.
Syntax
SELECT *
FROM table1 NATURAL JOIN table2;
With our example the query will be:
SELECT *
FROM Student NATURAL JOIN StudentCourse;
Output
This post aims to assist those learning SQL or any database-related courses. Feel free to leave comments if you spot any inaccuracies or if you'd like to share additional insights on the discussed topic. Your feedback is appreciated!
Sources:
https://www.sqlshack.com/an-introduction-to-sql-tables/
https://en.wikipedia.org/wiki/Join_(SQL)
https://www.geeksforgeeks.org/sql-join-set-1-inner-left-right-and-full-joins/
Top comments (0)