Download the BYJU'S Exam Prep App for free GATE/ESE preparation videos & tests - Download the BYJU'S Exam Prep App for free GATE/ESE preparation videos & tests -

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?

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

Q1

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

Q2

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.
Q3

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.

Q4

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,

Comments

Leave a Comment

Your Mobile number and Email id will not be published.

*

*