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
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,
- Cartesian Join in SQL
- Left Join in SQL
- Equi Join in SQL
- Right (Outer) Join in SQL
- Full Join in SQL
- Self Join in SQL
- Constraints in SQL
- Introduction to DBMS
- File Organization in DBMS
- Types of Keys in DBMS
- Decomposition in DBMS
- Normal Forms in DBMS
- Join Dependency in DBMS
- Relational Model in DBMS
- Entity-Relationship Model in DBMS
- Transaction in DBMS
- Indexed Sequential Access Method (ISAM)
- Data Control Language
- Introduction to SQL
Comments