SQL JOIN Tables – How to Join Tables in SQL

SQL JOIN Tables – How to join tables in SQL? It is the most popular question that is frequently used in SQL and asked in an interview as well. Developers always need to join many tables based on their project requirements. If you are a developer and also searching queries for joining tables then you are reading the best tutorial.

SQL JOIN Query

SQL Join Query is created with select statements and different types of join keywords. It can create a new table by selecting matching or no matching records from the two or more stables. with the help of it, we can handle large records of multiple tables.

Read also –

SQL Interview Questions and answers

Creating a Table in SQL

You have to join tables using SQL JOIN. So, You should know the basic information about it.

SQL JOIN

SQL JOIN is used to join more than one table based on a common column between them.

It returns the result of a new table by combining rows & columns of both tables.

SQL JOIN Types

There are different types of joins that are defined in SQL

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

INNER JOIN

INNER JOIN returns a new result table by joining two or more tables if values are matched in each common column of all the tables.

LEFT JOIN

LEFT JOIN returns a new result table by combining all the rows from the left table and matching rows from the right tables. if the rows of left tables do not have matching values then it will contain a NULL value.

• The Left Table remains before the LEFT JOIN keyword on the left side.
• The Right Table remains after the LEFT JOIN keyword on the right side.
• LEFT OUTER JOIN can be used instead of LEFT JOIN

RIGHT JOIN

Right JOIN returns a new result table by combining all the rows from the right table and matching rows from the left tables. if the rows of right tables do not have matching values then it will contain a NULL value.

• The Left Table remains before the RIGHT JOIN keyword on the left side.
• The Right Table remains after the RIGHT JOIN keyword on the right side.
• RIGHT OUTER JOIN can be used instead of RIGHT JOIN

FULL JOIN

FULL JOIN returns a new result table by combining all the rows of the left table & right table. if the rows do not have matching values then they will contain NULL values.

• The Left Table remains before the FULL JOIN keyword on the left side.
• The Right Table remains after the FULL JOIN keyword on the right side.
• FULL OUTER JOIN can be used instead of FULL JOIN

SQL JOIN Diagram

See the following SQL JOIN Diagram to understand the concepts of different types of SQL JOIN.

SQL JOIN Syntax

SQL JOIN Syntax has different types of syntaxes based on Jo. These syntaxes will help you to join multiple tables quickly.

Here I have created the following syntaxes for two tables like table1 & table2. You can easily create syntax for more tables as well.

INNER JOIN Syntax –

Syntax -1

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2
FROM table1 
INNER JOIN 
table2 ON table1.matching_column_name = table2.matching_column_name;

Syntax – 2

LEFT JOIN Syntax

Syntax -1

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2
FROM table1 
LEFT JOIN 
table2 ON table1.matching_column_name = table2.matching_column_name;

Where

• table1 is the left table
• table2 is the right table
• matching_column_name is the common column of both tables

Syntax – 2

SELECT table1.column1, table1.column2, table2.column1, table2.column2
FROM table2 
LEFT JOIN 
table1 ON table2.matching_column = table1.matching_column;

Where

• table1 is the right table
• table2 is the left table
• matching_column_name is the common column of both tables

RIGHT JOIN Syntax –

Syntax -1

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2
FROM table1 
RIGHT JOIN 
table2 ON table1.matching_column_name = table2.matching_column_name;

Where

• table1 is the left table
• table2 is the right table
• matching_column_name is the common column of both tables

Syntax – 2

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

Where

• table1 is the right table
• table2 is the left table
• matching_column_name is the common column of both tables

FULL JOIN Syntax –

Syntax -1

SELECT table1.column_name1, table1.column_name2, table2.column_name1, table2.column_name2
FROM table1 
FULL JOIN 
table2 ON table1.matching_column_name = table2.matching_column_name;

Where

• table1 is the left table
• table2 is the right table
• matching_column_name is the common column of both tables

Syntax – 2

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

Where

• table1 is the right table
• table2 is the left table
• matching_column_name is the common column of both tables

SQL JOIN Example

SQL JOIN example has four examples based on types of join.

There are two tables like students and mobile_numbers to create a SQL JOIN example. Both have the following table structure –

Table Name – students

Table Name – contact_info

INNER JOIN Example –

If you want to get matching values of a common column from both tables then you will have to write the following INNER JOIN Query.

Query – 

SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
FROM students 
INNER JOIN 
contact_info ON students.student_id = contact_info.student_id;

Output – 

LEFT JOIN Example

If you want to get all the values of rows from the left table and matching values of common rows from the right table then you will have to write the following LEFT JOIN Query.

Query – 

SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
FROM students 
LEFT JOIN 
contact_info ON students.student_id = contact_info.student_id;

Output – 

RIGHT JOIN Example –

If you want to get all the values of rows from the right table and matching values of common rows from the left table then you will have to write the following RIGHT JOIN Query.

Query – 1

SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
FROM students 
RIGHT JOIN 
contact_info ON students.student_id = contact_info.student_id;

Output – 

FULL JOIN Example –

If you want to get all the values of a common column from both tables either matching values exist or not then you will have to write the following INNER JOIN Query.

Query – 1

SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
FROM students 
FULL JOIN 
contact_info ON students.student_id = contact_info.student_id;

Output – 

SQL JOIN Conditions

SQL JOIN Condition has different types of clauses such as WHERE, ORDER BY, GROUP BY  & more. These clauses are mainly used with the select statement to join multiple tables based on different conditions.

WHERE Clause

When you use the SQL JOIN with WHERE clause then it will return a new result table where the column gender  has values male.

SELECT students.full_name, students.gender, contact_info.mobile_number, contact_info.email_address
FROM students
INNER JOIN
contact_info ON students.student_id = contact_info.student_id
WHERE employees.gender = 'male';

ORDER BY Clause

When you use SQL JOIN with ORDER BY clause then it will return a new result table with the alphabetical order of full_name.

ELECT students.full_name, contact_info.mobile_number, contact_info.email_address
FROM students
INNER JOIN
contact_info ON students.student_id = contact_info.student_id
ORDER BY students.full_name;

GROUP BY Clause

When you use the SQL JOIN with GROUP BY clause then it will return a new result table group by gender.

ELECT students.full_name, students.gender, contact_info.mobile_number, contact_info.email_address
FROM students
INNER JOIN
contact_info ON students.student_id = contact_info.student_id
GROUP BY students.gender;

How to Join Tables in SQL query

To join tables, you have to configure the following points –

• First of all, make sure that the SQL package is installed on your computer
• Create MySQL Database
• Create tables in MySQL database
• Insert some records in all the tables
• Write SQL Query to join  created tables
• Make sure that all the tables must be in a relationship  with foreign key
• If a column of the first table has a foreign key then a column of the second table should be a foreign key.
• Matching values may be in all the tables.

 INNER JOIN –

You can join tables using the INNER JOIN keyword.

mysql>CREATE DATABASE my_db;
mysql>USE my_db;
mysql>CREATE TABLE students
      ->(
      ->student_id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      ->full_name varchar(50),
      ->gender varchar(10),
      ->address varchar(100)
      ->);
mysql>INSERT INTO students(full_name, gender, address)
     ->VALUES('Noor Khan', 'Male', 'Patna'),
     ->('Avneesh Mishra', 'Male', 'New Delhi'),
     ->('Monika Singh', 'Female', 'Jaipur'),
     ->('Aaliya khan', 'Female', 'Chennai'),
     ->('Sunil Kumar', 'Male', 'Noida');
mysql>CREATE TABLE contact_info
      ->(
      ->contact_id int(10) PRIMARY KEY NOT NULL AUTO_INCREMENT,
      ->mobile_number int(20),
      ->email_address varchar(200),
      ->student_id int,
      ->CONSTRAINT student_id_fk
      ->FOREIGN KEY(student_id) REFFERENCES students(student_id)
      ->);
mysql>INSERT INTO contact_info(contact_id, mobile_number, email_address, student_id)
     ->VALUES(101, '1234567890', 'aaaaaa@gmail.com ', 3 ),
     ->(102, '2222222222', 'bbbbbb@gmail.com', 3 ),
     ->(103, '1111111111', 'cccccc@gmail.com', 1 ),
     ->(104, '3333333333', 'dddddd@gmail.com', 5 ),
     ->(105, '4444444444', 'eeeeee@gmail.com', 3 ),
     ->(106, '5555555555', 'ffffff@gmail.com', 5 ),
     ->(107, '6666666666', 'gggggg@gmail.com', 12 ),
     ->(108, '7777777777', 'hhhhhh@gmail.com', 15 ),
     ->(109, '8888888888', 'iiiiii@gmail.com', 10 ),
     ->(110, '0897654321', 'jjjjjj@gmail.com', 20 );
mysql>SELECT * FROM students;
mysql>SELECT * FROM contact_info;
mysql>SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
     ->FROM students 
     ->INNER JOIN 
    ->contact_info ON students.student_id = contact_info.student_id;yee_id = departments.department_name;

LEFT JOIN –

You can also join tables using the LEFT JOIN keyword.

mysql>USE my_db;
mysql>SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
     ->FROM students 
     ->LEFT JOIN 
    ->contact_info ON students.student_id = contact_info.student_id;yee_id = departments.department_name;

 RIGHT JOIN –

You can join tables using the RIGHT JOIN keyword

mysql>USE my_db;
mysql>SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
     ->FROM students 
     ->RIGHT JOIN 
    ->contact_info ON students.student_id = contact_info.student_id;yee_id = departments.department_name;

FULL JOIN –

You can join tables using the FULL JOIN keyword

mysql>USE my_db;
mysql>SELECT students.full_name, contact_info.mobile_number, contact_info.email_address
     ->FROM students 
     ->FULL JOIN 
    ->contact_info ON students.student_id = contact_info.student_id;yee_id = departments.department_name;

Tutorial Summary

Dear Developer, I have this tutorial according to my working experience. I hope that It will be useful to implement a SQL JOIN Table. Even this tutorial will be more helpful for the Interview.

If you have any questions related to SQL, Kindly ask me through the comment box. Even you can suggest sharing another web development tutorial.