Join Query in SQL

We use the SQL Join statement to combine data/rows from multiple tables on the basis of a common field between all of them.

In this article, we will take a look at the Join Query in SQL according to the GATE Syllabus for CSE (Computer Science Engineering). Read ahead to learn more.

Table of Contents

What is a Join Query in SQL?
- Example
Practice Questions on Join Query in SQL
FAQs

What is a Join Query in SQL?

We use the SQL Joins whenever we want to combine records from multiple tables in the given database. Thus, a JOIN is a means of combining fields from a couple of tables using values that are common to each of these.

Example

Let us consider the two following tables:

Table 1 − CONSUMERS Table

ID	CUST_NAME	CITY	AGE	SALARY
C1	Ramesh	Ahmedabad	32	2000.00
C2	Khilan	Delhi	25	1500.00
C3	Kaushik	Kota	23	2000.00
C4	Chaitali	Mumbai	25	6500.00
C5	Hardik	Bhopal	27	8500.00
C6	Komal	MP	22	4500.00
C7	Muffy	Indore	24	10000.00

Table 2 − REQUIREMENTS Table

ID	DATE	CONSUMER_ID	AMOUNT
C102	2021-10-08 00:00:00	C3	3000
C100	2020-10-08 00:00:00	C3	1500
C101	2018-11-20 00:00:00	C2	1560
C103	2019-05-20 00:00:00	C4	2060

Now, if we join these given two tables in the SELECT statement, we will do it as shown below:

SQL> SELECT ID, CUST_NAME, AGE, AMOUNT

FROM CONSUMERS, ORDERS

WHERE CONSUMERS.ID = ORDERS.CONSUMER_ID;

As a result, this would produce the following:

ID	NAME	AMOUNT	AGE
C3	Kaushik	3000	23
C3	Kaushik	1500	23
C2	Khilan	1560	25
C4	Chaitali	2060	25

Here, you can notice that we perform this join in the WHERE clause. Now, we can use several operators to join tables, like =, >, <, <>, >=, <=, !=, LIKE, BETWEEN, and NOT. All of these are used to join the given tables. However, out of all of these, the equal to symbol is the most common operator.

Different types of joins are available in SQL, such as:

1. INNER JOIN − It returns rows in case there is a match between both the available tables. It is also known as EQUI Join.

2. RIGHT JOIN − It returns all the rows from the right table, even when there is no match in the left table.

3. LEFT JOIN − It returns all the rows from the given left table, even when there is no match in the right table.

4. FULL JOIN − It returns the rows whenever there’s a match in one of the available tables.

5. CARTESIAN JOIN − It returns the Cartesian product of all the sets of records from multiple joined tables.

6. SELF JOIN − It is used whenever we want to join any table to itself like the given table were two tables while renaming at least one of the tables in the SQL statement temporarily.

Practice Questions on Join Query in SQL

1. Which of these is also known as INNER Join in SQL?

A. NON-EQUI JOIN

B. SELF JOIN

C. EQUI JOIN

D. None of the above

Answer: C. EQUI JOIN

2. Which of these joins do we use when we want to return those rows that do not consist of any matching values?

A. OUTER JOIN

B. EQUI JOIN

C. NATURAL JOIN

D. None of the above

Answer: A. OUTER JOIN

FAQs

What is a join query in SQL?

We use the SQL Join statement to combine data/rows from multiple tables on the basis of a common field between all of them. There are different types of Joins, namely:
a. INNER JOIN
b. LEFT JOIN
c. RIGHT JOIN

What are the 3 types of joins in SQL?

Here are the different types of JOINs present in SQL:

(INNER) JOIN: It returns the records that consist of matching values in both the tables.
LEFT (OUTER) JOIN: It returns all the records from the available left table and the records matched from the available right table.
RIGHT (OUTER) JOIN: It returns all the records present in the right table and the records that we matched from the left table.

What is the use of a join query?

We use SQL Join to fetch data/information from multiple tables. We join these to appear as a single set of data. Thus, it is used when we want to combine columns from multiple tables using values that are common to both the given tables. We use the JOIN Keyword in SQL queries whenever we want to join multiple tables.

What is a joining table?

A join table refers to a data table that consists of multiple outgoing connections that connects multiple data tables to just a single data table.

Keep learning and stay tuned to get the latest updates on GATE Exam along with GATE Eligibility Criteria, GATE 2023, GATE Admit Card, GATE Syllabus, GATE Previous Year Question Paper, and more.

Also Explore,