SQL Interview Questions and Answers - Part 09
Are you preparing for the SQL Interview? If yes, then you are at the right place. This is the SQL Interview Questions and Answers article series. Here we will see Top 100+ SQL Interview Questions with Answers.
Please visit our YouTube Channel for Interviews and other videos by below link:
Please read the complete Design Pattern, C#, MVC, WebAPI, and .Net Framework Interview Questions and Answers article series here.
Introduction
This is the 09th part of the SQL Interview Questions and Answers article series. Each part contains eight and ten SQL Interview Questions with Answers. Please read all the SQL Interview Questions list here.
I will highly recommend to please read the previous parts over here before continuing the current part:
SQL Interview Questions and Answers - Part 09
Q68. What is JOIN in SQL? What are the different types of joins available in SQL?
In SQL, Joins are used to retrieve the data from multiple tables. When we join two tables, the first table is always known as Left Table and the second table is known as the Right Table.
There are mainly following three categories of JOINs in SQL:
- INNER JOIN
- OUTER JOIN
- CROSS JOIN
Please read more about all the joins in detail here.
Q69. What is INNER JOIN in SQL?
An INNER JOIN is the default ANSI JOIN. The INNER JOIN is used to retrieve only the matching records from both tables. INNER JOIN discards all the non-matching rows from both tables.
Since the INNER JOIN is the DEFAULT JOIN, if you do not provide any join name, it will be treated as INNER JOIN by default. It is also known as EQUI JOIN as it uses only the Equality Operator.
SELECT
Name,
Email,
MobileNumber,
RoleName
FROM Employee E
INNER JOIN EmployeeRole R ON E.RoleId = R.Id
In the INNER JOIN, the order of the table does not matter. So if you change the order of the table the result will still be the same.
Please watch the INNER JOIN video here for more details.
Q70. What is Self JOIN in SQL? How it is different from INNER JOIN?
A SELF JOIN is not any separate kind of join. SELF JOIN is just like any other join except that two instances of the same table will be joined in the query. There is no keyword like "SELF" in SQL.
Joining a table with itself is called a SELF JOIN. When we have some relation between the columns within the same table then we use the SELF JOIN.
When we implement a SELF JOIN, we create the alias for the table and we can create any number of aliases for a single table. Without alias names, we cannot implement SELF JOIN.
One of the common scenarios of SELF JOIN is the Employee Table which contains Employee and Manager mapping in the same table. So to fetch the employee and their manager we have to use the SELF JOIN.
Similarly, one more example for SELF JOIN is the Country-State or State-Capital table. In order to get the country and their state or state and their capital, we have to use the SELF JOIN.
SELECT
S.Name AS StateName,
C.Name AS CapitalName
FROM States S
INNER JOIN States C ON S.StateID = S.ID
Q71. What is LEFT JOIN in SQL? What is the difference between LEFT JOIN and INNER JOIN?
The LEFT OUTER JOIN returns all the records from the first table (Left Table) and only the matching records from the second table (Right Table).
So the LEFT JOIN basically does the following three things:
- Returns the matching records from both tables.
- Returns the non-matching records from the first table (Left Table).
- Returns (or Set) the NULL values for the corresponding non-matching rows of the second table (Right Table).
One very important thing which you have to keep in mind is that the order of the table matters in the case of LEFT JOIN. So if you change the order of the tables, the result will also be changed. Therefore you have to decide first, which table you have to use as a base table (Left Table or First Table).
SELECT
Name,
Email,
MobileNumber,
RoleName
FROM Employee E
LEFT OUTER JOIN EmployeeRole R ON E.RoleId = R.Id
One more thing which you have to keep in mind is that a WHERE clause in the LEFT JOIN turned the LEFT JOIN into the INNER JOIN.
Please watch the LEFT JOIN video here for more details. You can also watch the INNER JOIN vs LEFT JOIN video here.
Q72. What is the difference between RIGHT JOIN and LEFT JOIN in SQL?
The RIGHT OUTER JOIN returns all the records from the second table (Right Table) and only the matching records from the first table (Left Table). In short, the RIGHT JOIN is just the reverse of the LEFT JOIN. You can omit the "OUTER" keyword.
So the RIGHT JOIN basically does the following three things:
- Returns the matching records from both tables.
- Returns the non-matching records from the second table (Right Table).
- Returns (or Set) the NULL values for the corresponding non-matching rows of the first table (Left Table).
SELECT
Name,
Email,
MobileNumber,
RoleName
FROM Employee E
RIGHT OUTER JOIN EmployeeRole R ON E.RoleId = R.Id
The order of the table matters in the case of RIGHT JOIN as well same as the LEFT JOIN. So if you change the order of the table, the result will also be changed.
Q73. What is FULL JOIN in SQL?
The FULL OUTER JOIN is the combination of both LEFT JOIN and RIGHT JOIN. So FULL OUTER JOIN returns both matching and non-matching data from both the tables.
The result of the UNION of both the LEFT JOIN and RIGHT JOIN is the same as the FULL JOIN.
So a FULL JOIN basically does the following three things:
- Returns the matching records from both tables.
- Returns the non-matching records from the first table (Left Table). And also it returns (or Set) the NULL values for the corresponding non-matching rows of the second table (Right Table).
- Returns the non-matching records from the second table (Right Table). And also it returns (or Set) the NULL values for the corresponding non-matching rows of the first table (Left Table).
SELECT
Name,
Email,
MobileNumber,
RoleName
FROM Employee E
FULL OUTER JOIN EmployeeRole R ON E.RoleId = R.Id
The order of the table does not matter in the case of FULL JOIN, unlike the LEFT JOIN and RIGHT JOIN. But the order of records may be changed when you change the table order.
Please watch the FULL JOIN video here for more details. Please watch the UNION vs UNION ALL video here for more details.
Q74. What is CROSS JOIN in SQL? What is the use of Cross Join?
The CROSS JOIN returns the Cartesian Product of the table records. The CROSS JOIN does not have an ON clause to specify the JOIN Condition. If you try to do so, you will get a syntax error.
In CROSS JOIN, each record of the first table is joined with each record of the second table. So the result size of a CROSS JOIN is the number of rows in the first table multiplied by the number of rows in the second table.
The order of the table does not matter here as well. If you change the order of tables, the total number of records still will be the same. But the order of the records may change when you change the table order.
SELECT
Name,
Email,
MobileNumber,
RoleName
FROM Employee E
CROSS JOIN EmployeeRole R
We can achieve the CROSS JOIN result using the NON-ANSI JOIN as well.
SELECT
Name,
Email,
MobileNumber,
RoleName
FROM EmployeeRole R, Employee E
Q75. What is the difference between FULL JOIN and CROSS JOIN in SQL?
A cross join produces a Cartesian Product between the two tables, returning all possible combinations of all rows. On the other hand, a FULL JOIN is a combination of a LEFT and RIGHT JOIN; which returns all the matching and non-matching records from both the tables.
The CROSS JOIN has no ON clause because you're just joining everything to everything. But the FULL JOIN has an ON clause to specify the joining condition.
Q76. What is the difference between ON Clause and WHERE clause in SQL?
ON is basically used to define the join condition while WHERE is used to filter the data. WHERE clause can be used to update and delete records as well but ON can not be.
WHERE can be used to filter the data further which is retrieved by joining the ON clause or without the ON clause (CROSS JOIN).
SELECT *
FROM User U
JOIN Role R ON U.RoleId = R.RoleId
WHERE R.RoleName = 'Admin'
However, sometimes we can use the WHERE filter condition in the ON join condition itself. JOINing in the WHERE clause can be confusing since this is not its typical purpose.
SELECT *
FROM User U
JOIN Role R ON U.RoleId = R.RoleId AND R.RoleName = 'Admin'
So both the ON and WHERE clauses can be used to filter data in a query. There are readability and accuracy concerns to address with filtering in the ON clause. Filtering in the ON clause may produce unexpected results when using a LEFT, RIGHT, or OUTER JOIN.
SELECT * FROM facebook F
LEFT JOIN linkedin L ON F.name = L.name
WHERE F.city = 'US';
SELECT * FROM facebook F
LEFT JOIN linkedin L ON F.name = L.name AND F.city = 'US';
The above two queries will not produce the same output. Because the first query becomes the same as INNER JOIN while the second query is LEFT JOIN.
Sometimes writing a query in a different way can yield speed improvements. Using WHERE or ON to JOIN the data should produce the same query plan.
Please watch the ON Clause vs WHERE Clause video here.
Q77. What is the difference between INNER JOIN and IN() clause?
Both IN and JOIN are two different queries that can yield different results. JOIN is used to combine columns from multiple tables while IN is used to filter records from a single table based on single column values against a list of values.
The IN statement can be used to find rows in a query where one column can be matched to a value in a list of values. The list of values can be hard-coded as a comma-separated list or can come from a sub-query. IN statements are easy to write and understand. The only downside is that they can only compare a single column from the subquery to a single column from the main query.
If two or more values need to be compared then the IN statement cannot be used. Also, you cannot return columns from multiple tables by using IN clause. However, you can use other table columns within the IN Sub-query. On the other hand, JOIN allows one or more columns to be used to find matches and you can combine and return columns from multiple tables as well.
The IN operator ignores the duplicate records from the IN sub-query and produces only unique results. The downside to JOIN is that if the subquery has any identical rows based on the JOIN predicate, then the main query will repeat rows which could lead to invalid query outputs.
Q78. What is the difference between JOIN and UNION in SQL?
JOIN in SQL is used to combine data from multiple tables based on a matched condition between them. while UNION is used to combine the result-set of two or more SELECT statements.
The data combined using the JOIN statement results in new columns while the data combined using the UNION statement results in new rows.
In JOIN the number of columns selected from each table may not be the same. But In UNION the number of columns selected from each table should be the same.
In JOIN the datatypes of corresponding columns selected from each table can be different. But in UNION the datatypes of corresponding columns selected from each table should be the same.
Please watch the JOIN vs UNION video here.
To Be Continued Part-10...
Recommended Articles
Thanks for visiting this page. Please follow and join us on LinkedIn, Facebook, Telegram, Quora, YouTube, Twitter, Pinterest, Tumbler, and VK for regular updates.
Posts
No comments:
Post a Comment
Please do not enter any HTML. JavaScript or spam link in the comment box.