Myth Busters in SQL – Structured Query Language

In this article, we are going to bust some of the common myths we usually have while working on SQL – Structured Query Language. We will be using the MS SQL Server Database Server to write our queries.

1. HAVING Clause cannot be used without GROUP BY Clause!

You must be wondering whether the HAVING Clause can be used in a query without using the GROUP BY Clause!

Why does this question even arise? Because we have learned: “HAVING is used with GROUP BY”. Let’s get to the answer!

We can definitely “use the HAVING Clause in a query without even using the GROUP BY Clause” but it doesn’t really make sense as there is no such practical use case of this query. Why?

Because HAVING is used to filter the data after grouping it with respect to a particular column in the table

Want to see how?

Consider the ORDERS table with columns like Order_Id, Category, Product, and Sales

SELECT * FROM ORDERS

HAVING without GROUP BY

We write the query as :

SELECT AVG(Sales) AS Avg_Sales
FROM ORDERS
HAVING AVG(Sales) > 500

We get the output as :

So technically, as we can see HAVING is working perfectly without GROUP BY but we can’t really analyze much from this.

2. A Foreign Key is required to join 2 tables!

You must be wondering whether a primary-foreign key relation is required between 2 tables to join them.

Let’s take an example. We have 2 tables :

Table Employee: It contains the details of various employees in the Technical department of the organization “ABC”.

Table Courses: It contains the Course_Id of the courses taken by different employees in the organization “ABC”

As we can see no relationship exists between the Table Employee and the Table Courses. (E_Id is not a foreign key as it does not take its values from the E_Id column of the Employee table.)

Writing a query to Join the 2 tables :

SELECT E.E_Id , E_Name , Department ,Salary , Course_Id FROM Employee E INNER JOIN Courses C
 ON E.E_Id = C.E_Id

We get the output as :

As we can see: This myth is busted!

We are able to join the 2 tables without any primary/foreign key just on the basis of 1 column with the same data type (E_Id Column)

What can we Conclude?

To join 2 tables :

We don’t require a Primary Key.

We don’t require a Foreign Key.

We don’t require the tables to have any relationship.

We can join 2 tables on any column(s) having the same data type.

3. SELF is the keyword used to put a SELF JOIN!

For all the other joins we have keywords like INNER JOIN, LEFT JOIN. RIGHT JOIN and FULL JOIN. You must be wondering what is the keyword for a SELF JOIN!

Let’s bust the myth!

There is no such keyword for putting a SELF JOIN. SELF JOIN means joining a table with itself on any column. Here 2 aliases of the same table are created. Let’s take an example :

Consider the following table: Employee

Let’s take a question. Display the name of the employees who belong to the same department.

Here the Employee table needs to be joined to itself. To perform a self join here, we can write the query as :

SELECT A.E_Name AS EMP1 , B.E_Name AS EMP2 , A.Department
FROM Employee A , Employee B
WHERE A.E_Id  B.E_Id
AND A.Department = B.Department

It needs to be noticed that we didn’t use any keyword for performing the SELF JOIN here, instead of table aliases were created and used to actually join the Employee table with itself.

4. ORDER BY Clause cannot be used in a Subquery!

 Let us consider 2 examples first :

Consider the table: ORDERS

Example 1:

We want to display all the data in the table and additional columns showing Products ordered by Sales in ascending order. To do that we can write a query :

SELECT * , (SELECT Product , Sales FROM ORDERS ORDER BY Sales)
FROM ORDERS

But here we get an error !!!

“The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP, OFFSET or FOR XML is also specified”

But why this error?

In this case, the subquery (SELECT Product, Sales FROM ORDERS ORDER BY Sales) returns more than one row.

Example 2:

Let’s suppose I write a query like :

SELECT Product , (SELECT TOP 1 Product FROM ORDERS ORDER BY Sales) AS Highest_Sale 
FROM ORDERS

This query works!

In this case, the subquery (SELECT TOP 1 Product FROM ORDERS ORDER BY Sales) returns only one row (due to the TOP keyword used).

So we get that: In Example 1, the ORDER BY Clause is not working in the subquery. While In Example 2, the ORDER BY Clause is working in the subquery. But did you notice something there?

1. The TOP keyword has been used in the query.

2. We can’t really analyze anything from the query. So no useful information can be derived from the query.

Conclusion

What can we think of?

We already know that the output of the inner query (the subquery) is used as an input for the outer query (the main query) , therefore it is not required that we order the values in the subquery because we anyway have to do it in the outer query. Hence, it is not very useful to use the ORDER BY Clause in the subquery.

What does SQL say?

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions unless TOP, OFFSET, or FOR XML is also specified. So it is preferable not to use the ORDER BY Clause in a subquery because :

1. It does not provide any valuable information.

2. Can only be used with TOP, OFFSET, FOR XML Clauses

5. WHERE Clause and HAVING Clause can be used interchangeably!

Consider the ORDERS table with columns like Order_Id, Category, Product, and Sales

SELECT * FROM ORDERS

Suppose we want to see the products whose sales are greater than 500. We will write the query as :

SELECT Product , Sales FROM ORDERS
WHERE Sales > 500

We get the output as :

Suppose we want to see the categories whose sales are greater than 500. We will write the query as :

SELECT Category , SUM(Sales) AS SALES FROM ORDERS
GROUP BY Category 
HAVING SUM(Sales) > 500

We get the output as :

In both cases, we have to filter the records on the basis of some condition but the level is different.

When we want to see the categories we need to group the data i.e. we take all the products falling in a category and create a group. When we want to see the products no grouping is required.

What can we conclude?

WHERE Clause and HAVING Clause cannot be used interchangeably. Both are used to filter the records from a table but at different levels.

WHERE Clause: Used to filter the records from the table based on the specified condition.

HAVING Clause: Used to filter the records from the groups formed based on the specified condition.

6. Choosing ‘which JOIN to put when’ is NOT a big deal!

There are various types of joins that can be put on the tables to get the desired results namely: Left Join, Right Join, Inner Join, Full Join, Self Join, Cross Join, Natural Join, etc.

Do all these joins result in the same output? Can we use these joins interchangeably? Using any of these joins in any situation will serve our purpose? Want answers to these questions?

Let’s take a situation first, We have 2 tables :

Table: Customer

Table: Orders

Display the details of all those customers who have placed the order.

How will we solve this? We need to put a join to retrieve data from these 2 tables. But which join needs to be put in this case?

Let’s try LEFT JOIN first! The query can be written as:

SELECT * FROM Customer LEFT JOIN Orders ON Cust_Id = Order_Id

This gives the following output:

We are getting the details of all the customers from the Customer table. Although we can get information about those customers who have placed an order by seeing the NULL’s in the Order_Id column but that requires manual work.

Not working!

Let’s try RIGHT JOIN now. The query can be written as:

SELECT * FROM Customer RIGHT JOIN Orders ON Cust_Id = Order_Id

This gives the following output:

As desired!

We get the details of only those customers who have placed an order i.e. have a matching record in the Orders table. Since we want all the records from the Orders table and the matching records from the Customer table so that we can get the details of only those customers who have placed an order and hence are in the Orders table. So, In this scenario, we will go with the RIGHT JOIN.

What can we conclude?

Putting the correct join can help us save a lot of time and energy! We must know which join to apply in which scenario. This is a really important concept that makes our life easy while querying data!

Let’s learn a bit about the most frequently used joins :

1. (INNER) JOIN: It retrieves the records that have matching values in both the tables involved in the join.

2. LEFT (OUTER) JOIN: It retrieves all the records from the left table and corresponding matching records from the right table.

3. RIGHT (OUTER) JOIN: It retrieves all the records from the left table and corresponding matching records from the right table.

4. FULL (OUTER) JOIN: It retrieves all the records from both the tables whether there is a match or not.

So I hope this article of mine busted some of your myths with respect to SQL. If you still have any queries with respect to these myths, do let me know in the comments below. We can get on a quick chat there.

To further enhance your knowledge you can refer to the article: https://www.analyticsvidhya.com/blog/2020/07/sql-functions-for-data-analysis-tasks/

You can connect with me on LinkedIn: https://www.linkedin.com/in/ayushi-gupta25/