Playing as a guest? to earn XP and track your progress.
🔗 JOINS AND SET OPERATIONS (SQL) Quiz
🔗 JOINS AND SET OPERATIONS (SQL)
What is the primary purpose of a JOIN in SQL?
To delete rows from multiple tables
To combine rows from two or more tables based on a related column
To sort data in ascending order
To create a new table structure
A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Which type of join returns the Cartesian product of rows from the tables?
Equi Join
Natural Join
Cross Join
Left Join
A Cross Join (or Cartesian Product) returns all possible combinations of rows from the tables involved, without any condition.
If Table A has 5 rows and Table B has 3 rows, how many rows will be in the output of a Cartesian Product?
8
15
2
5
The cardinality of a Cartesian product is the product of the number of rows in both tables (5 * 3 = 15).
Which symbol is used to represent the equality condition in an Equi-Join?
==
=
<>
LIKE
An Equi-Join uses the equality operator (=) to match values in the common columns.
What is a characteristic feature of a Natural Join compared to an Equi-Join?
It uses a non-equality operator
It returns the common column twice
It automatically removes duplicate columns
It requires the ON clause
A Natural Join automatically joins tables based on same-named columns and removes the duplicate column from the result set.
Which clause is used to define a Table Alias?
AS
ALIAS
NAME
SET
The keyword AS is used to assign an alias to a table (e.g., FROM emp AS e), though it can also be omitted (FROM emp e).
In a Natural Join, what happens if the two tables have no columns with the same name?
It returns an empty set
It returns a Cartesian Product
It throws an error
It performs an Equi-Join
If there are no common columns, a Natural Join behaves like a Cross Join (Cartesian Product).
Which join returns all records from the left table and the matched records from the right table?
Inner Join
Right Join
Left Join
Cross Join
A LEFT JOIN returns all rows from the left table and matching rows from the right table; unmatched rows get NULL values.
What value is displayed for non-matching rows in the right table when performing a LEFT JOIN?
0
False
NULL
Empty String
In outer joins (Left/Right), if no match is found, the columns from the other table are filled with NULL.
Identify the type of join in the following query: SELECT * FROM Emp, Dept;
Equi Join
Natural Join
Cartesian Product
Outer Join
Listing multiple tables in the FROM clause without a WHERE condition results in a Cartesian Product (Cross Join).
Which of the following is NOT a valid type of Join in SQL?
Outer Join
Inner Join
Reverse Join
Self Join
'Reverse Join' is not a standard SQL join type.
Which operator is used to join two tables in a Non-Equi Join?
=
LIKE
Operators other than = (e.g., <, >, <=)
AND
Non-Equi Joins use comparison operators other than equality, such as <, >, <=, or >=.
The degree of the Cartesian product of two tables is:
Product of degrees of both tables
Sum of degrees of both tables
Difference of degrees
Maximum of degrees
The degree (number of columns) of a Cartesian product is the sum of the columns of the participating tables.
Consider tables T1 (ID, Name) and T2 (ID, Salary). SELECT * FROM T1 NATURAL JOIN T2; will have how many columns?
4
3
2
5
T1 has 2 cols, T2 has 2 cols. Natural Join merges the common 'ID' column. Total columns = 2 + 2 - 1 = 3.
What is the prerequisite for using UNION in SQL?
Tables must have different column names
Selected columns must be of compatible data types and same number
Tables must have a primary key
Tables must have different number of rows
For SET operations like UNION, the number of columns and their data types must match in both queries.
Which Set Operation returns only the rows that appear in both queries?
UNION
MINUS
INTERSECT
CROSS
INTERSECT returns only the rows that are common to both result sets.
What does the MINUS operator do?
Adds rows from two tables
Returns rows from the first query that are not in the second
Returns common rows
Multiplies rows
MINUS returns distinct rows from the first query that do not appear in the second query.
How does UNION differ from UNION ALL?
UNION removes duplicates, UNION ALL keeps them
UNION keeps duplicates, UNION ALL removes them
There is no difference
UNION works on numbers, UNION ALL on strings
The standard UNION operator removes duplicate rows from the result, whereas UNION ALL includes all duplicates.
(PYQ) To join two tables Student and Course on column C_ID, which query is correct?
SELECT * FROM Student, Course WHERE Student.C_ID = Course.C_ID
SELECT * FROM Student JOIN Course WHERE C_ID = C_ID
SELECT * FROM Student, Course
SELECT * FROM Student CROSS JOIN Course
Option 0 is a valid Equi-Join syntax. Option 1 is ambiguous without table aliases/prefixes.
In a query SELECT * FROM A, B, if A has 10 records and B has 10 records, the result will have:
10 rows
20 rows
100 rows
0 rows
This is a Cartesian product: 10 rows * 10 rows = 100 rows.
Which keyword is used to specify the join condition in a modern JOIN syntax (e.g., INNER JOIN)?
WHERE
ON
WITH
BY
The ON keyword is used to specify the condition for joining tables in explicit JOIN syntax (e.g., JOIN ... ON ...).
If you perform a Natural Join on tables with NO common column names, it behaves exactly like:
Inner Join
Left Join
Cartesian Product
Right Join
Without a common column to match, a Natural Join cannot filter rows and thus produces a Cartesian Product.
Which of the following queries contains a Cartesian Product?
SELECT * FROM A JOIN B ON A.id = B.id
SELECT * FROM A, B WHERE A.id = B.id
SELECT * FROM A, B
SELECT * FROM A NATURAL JOIN B
The comma-separated list of tables without a WHERE clause creates a Cartesian Product.
(SQP) Identify the invalid Join type from the list:
Equi Join
Natural Join
Double Join
Self Join
'Double Join' is not a standard SQL term.
When using table aliases, can you still use the original table name in the SELECT list?
Yes, always
No, once an alias is defined, it must be used
Only in the WHERE clause
Only in the ORDER BY clause
In most SQL dialects, once a table alias is defined in the FROM clause, the original table name cannot be used elsewhere in the query.
Which join returns only rows that have matching values in both tables?
Inner Join
Left Join
Right Join
Full Outer Join
An Inner Join (or simple Join/Equi-Join) returns only the rows where there is a match in both tables.
In the query SELECT e.name, d.dept FROM emp e, dept d WHERE e.id = d.id, what are e and d?
Column aliases
Row aliases
Table aliases
Database aliases
e and d are Table Aliases defined in the FROM clause to shorten table references.
Consider Table A with columns (id, name) and Table B with columns (id, grade). A SELECT * FROM A, B WHERE A.id = B.id will return how many columns?
3
4
2
1
This is an Equi-Join using SELECT *. It returns all columns from both tables: id(A), name, id(B), grade. Total 4 columns.
The USING clause can be used as an alternative to ON when:
The columns being joined have different names
The columns being joined have the same name
Doing a Cartesian product
Using a Non-Equi join
The USING(column_name) syntax simplifies the join condition when the column names are identical in both tables.
(PYQ) Which SQL keyword is used to combine the result-set of two or more SELECT statements?
JOIN
MERGE
UNION
COMBINE
UNION is the set operator used to combine results from multiple SELECT statements.
Which of the following is true about a Primary Key?
It can allow NULL values
It can have duplicate values
It uniquely identifies each record
A table can have multiple Primary Keys
A Primary Key must be unique and cannot contain NULL values. A table can have only one Primary Key.
A Foreign Key is used to:
Ensure all values are unique
Link two tables together
Speed up queries
Store large text data
A Foreign Key is a field in one table that refers to the Primary Key in another table, establishing a link between them.
If you want to list all Departments that do NOT have any Employees assigned, which operation would you use (assuming standard tables)?
Inner Join
Left Join (Dept on Left) checking for NULL
Cartesian Product
Natural Join
You would use a LEFT JOIN (with Departments on the left) and filter for rows where the Employee ID is NULL.
What is the result of SELECT 5 FROM dual UNION SELECT 5 FROM dual?
Two rows of 5
One row of 5
Error
NULL
UNION automatically removes duplicates, so it returns the single distinct row '5'.
Which statement about Equi-Join is FALSE?
It uses the '=' operator
It can result in duplicate columns in the output
It joins every row with every other row regardless of condition
It is a type of Restricted Join
Equi-Join does NOT join every row with every other row; that is a Cartesian Product. Equi-Join filters based on equality.
In MySQL, which keyword is used to perform an intersection of two queries? (Note: MySQL doesn't natively support INTERSECT keyword directly in older versions, but concept applies to SQL standard)
INTERSECT
AND
MEET
COMMON
In standard SQL, the keyword is INTERSECT. (Note: In MySQL specifically, this is often simulated with IN or EXISTS, but INTERSECT is the standard term).
(SQP) What will be the cardinality of the table generated by: SELECT * FROM T1, T2 WHERE 1=1?
Sum of rows
Product of rows
0
1
The condition WHERE 1=1 is always true, so this query functions exactly like a Cartesian Product.
Which join condition is valid for a Self Join on table Emp?
WHERE Emp.id = Emp.id
WHERE A.id = B.mgr_id (where A and B are aliases for Emp)
WHERE Emp.id = Dept.id
WHERE Emp.id = NULL
A Self Join joins a table to itself. You must use different aliases (e.g., A and B) to distinguish the two instances of the table.
If Table A has columns (X, Y) and Table B has columns (Y, Z), which column(s) will be used for a Natural Join?
X
Y
Z
X and Z
Natural Join automatically selects columns with identical names in both tables. Here, 'Y' is the common column.
The columns in the result of a UNION operation are determined by:
The first SELECT statement
The second SELECT statement
Alphabetical order
Randomly
The column names in the final result set of a UNION are usually taken from the column names in the first SELECT statement.
Which error typically occurs if you reference a common column in an Equi-Join without a table alias (e.g., SELECT id FROM A, B)?
Syntax Error
Column ambiguous
Table not found
Logic Error
Ambiguity error occurs because SQL doesn't know which table's 'id' column you want to select.
In a Right Join between Table A (Left) and Table B (Right), which table's rows are fully preserved?
Table A
Table B
Both
Neither
A Right Join returns all rows from the Right table (Table B) and matching rows from the Left table.
(PYQ) Which of the following is correct syntax to display distinct cities from table Customer?
SELECT DISTINCT city FROM Customer
SELECT UNIQUE city FROM Customer
SELECT DIFFERENT city FROM Customer
SELECT city FROM Customer DISTINCT
The DISTINCT keyword is used immediately after SELECT to remove duplicates.
What is the maximum number of tables that can be joined in a single SQL query?
2
10
256 (depends on DBMS)
Unlimited
There is a limit, but it is usually very high (e.g., 61 or 256) and depends on the specific DBMS implementation, not strictly 2.
Can a WHERE clause be used with a JOIN...ON syntax?
No, never
Yes, to filter results after the join
Only with Left Join
Only with Natural Join
Yes, you can use ON for the join logic and WHERE to filter the resulting rows further.
Which of these is NOT a Set Operation?
UNION
SELECT
INTERSECT
MINUS
SELECT is a basic data retrieval command, not a Set Operation like Union, Intersect, or Minus.
If you want to combine results of two queries but keep all duplicates, which operator do you use?
UNION
UNION ALL
COMBINE
ADD
UNION ALL combines results and retains all duplicate rows.
(SQP) A table Orders has a foreign key CustID referencing Customer. What type of join allows us to see Orders alongside Customer names?
Cartesian Product
Equi Join / Inner Join
Self Join
Index Join
An Equi-Join (or Inner Join) on the foreign key/primary key relationship is the standard way to link these tables.
What happens if you try to UNION two queries with different numbers of columns?
It works and fills with NULL
It raises an error
It truncates extra columns
It joins them instead
It raises an error. The number of columns in all SELECT statements within a UNION must be the same.
Which join is also known as an 'Unrestricted Join'?
Natural Join
Equi Join
Cartesian Product
Inner Join
The Cartesian Product is called an Unrestricted Join because it has no join condition restriction.