MYSQL Concepts – Where, In, Having & Join Clauses Made Easy

by Dr. Alan October 09, 2018

While taking a tutoring session on SQL today, I realized that both of my students were not clear about the concepts of WHERE, IN, HAVING and JOINS. This made me think that perhaps many students might be having similar doubts primarily regarding their application. So, here I’ll try to simplify this theory as much as possible as well as tackle some frequently asked questions. But before we delve into the complexities, for the benefit of all students, let us have a quick look at the basics of these SQL concepts.

MySQL Joins explained

JOINS:

Joins are used to combine tables or rows based on a common field among them. SQL joins can be classified as:

  • INNER JOIN: Returns tuples with at least one match in both tables
  • LEFT JOIN/LEFT OUTER JOIN: Returns tuples from the left table along with the matched rows from the right table
  • RIGHT JOIN/RIGHT OUTER JOIN: Returns tuples from the right table and the matched rows from the left table
  • FULL JOIN: Returns tuples if there is a match in one of the tables

Examples:
SELECT o.orderId, c.customer_name
FROM orders o
INNER JOIN customers c
ON o.customerId=c.customerId;
This query returns the order id and name of the customers whose customer id in the order table and order table are the same.

SELECT o.orderId, c.customer_name
FROM customers c
LEFT JOIN orders o
ON c.customerId=o.customerId;
Above query performs a left join on the tables customer and order.

WHERE Clause:

This is used to specify exact rows to extract from SELECT,INSERT,UPDATE and DELETE statements based on a particular filtering condition.

Syntax:
SELECT FROM WHERE ;

Examples:
– SELECT salary
FROM employees
WHERE id = 123;
This is a single condition WHERE clause which returns the salary of the employees with employee id 123.

– SELECT salary
FROM employees
WHERE first_name = ‘Mary’ AND id = 123;
Here we use AND along with WHERE which means we are comparing 2 conditions and only if both are satisfied, i.e. the expression returns the salary of the employee whose name is Mary and id is 123.

IN Operator:

This operator is used to check if a specified value matches any value in a sub query or a list and also helps to reduce the number of OR operators in SELECT,INSERT,DELETE and UPDATE statements.

Syntax:
SELECT FROM WHERE IN <list/sub query>;

Examples:
– SELECT *
FROM account
WHERE branch_name IN(‘California’,’New York’);
Illustrating an example where IN operator is used with a list which will return all the account details from the branch either at California or New York.

– DELETE
FROM account
WHERE branch_name IN(SELECT b.branch_name
FROM branch b
WHERE b.city=’California’);
Illustrates the use of IN operator with a sub query which deletes all the tuples in the city California keeping branch name as common instance in the query and sub query.

HAVING Clause:

This clause is known to lay out filter conditions in aggregate functions.

Syntax:
SELECT , aggregate_function(column_name)
FROM WHERE
GROUP BY HAVING aggregate_function(column_name) operator value;

Examples:
SELECT studentId, MAX(marks)
FROM university
WHERE level = ‘Junior’
GROUP BY studentId
HAVING marks > 60;
This query will list the junior level students who have marks greater than 60 grouped in accordance to their id’s.

 

Frequently asked questions on the above clauses and operators

Now that we have revised the basics, let us go ahead and look at some of the specify questions my students have asked me in the past few classes. I did some research and saw that similar questions had been asked on various forums across the Internet. As a result, here I will put forth three questions I took notice which where really not easy to comprehend by many students.

Q. Can we use the WHERE and HAVING clauses in the same query?

A. Yes we can. WHERE and HAVING clause can both be used independently for their own functionalities as well as be combined in a single query to extract a specified result. In the example I provided above for the HAVING clause, we saw that the query extracted definite details for all students who were juniors. Now the same query can be divided into two independent queries as:

SELECT studentId
FROM university
WHERE level = ‘Junior’;
This query returns Id’s of all Junior students.
SELECT studentId, MAX(marks)
FROM university
GROUP BY studentId
HAVING marks > 60;

This query returns Id’s of all students with marks greater than 60 irrespective of being Juniors or Seniors.
Thus, we can use them together or separately as needed by the query, only make sure the syntax is correct. HAVING clause should be used only when there is a need to specify a condition in the GROUP BY clause. Remember, all GROUP BY clauses need not be followed by a HAVING clause, but all HAVING clauses necessarily should be preceded by a GROUP BY clause.

Q. Will the output of a WHERE IN clause be in the same order as the input?

A. Not necessarily. When an ordered list is provided as a parameter for the IN clause, the resultant queried output might not be in the same arrangement as in the input list. Let us consider an example:

SELECT studentId
FROM university
WHERE level IN (‘JR’,’SR’);

Now let us see how the database for the following query looks like:
+——+——-+
| id | level |
+——+——-+
| 1 | JR |
| 2 | SR |
| 3 | SR |
| 4 | JR |
+——+——-+
This query above will yield the following output:
+——+
| id |
+——+
| 1 |
| 2 |
| 3 |
| 4 |
+——+

Well, here we see that the output is the same ordered list as that of the input. But if in any case the output is not ordered, you can use the ORDER BY clause to sort the output in a particular order based on a single column or multiple columns in ascending(ASC) or descending(DESC) order. By default ORDER BY clause is set to sort in ascending order unless specified explicitly. Thus, we could have written the same query with an ORDER BY clause as:

SELECT studentId
FROM university
WHERE level IN (‘JR’,’SR’)
ORDER BY studentId;

This would explicitly sort the output in the order of the Id’s in the input list in ascending order.

Q. Can we use the JOIN clause after the WHERE clause?

A. No, there is a specified syntactical order followed by each clause. The syntax for a JOIN clause can be written as:

SELECT column_name
FROM table_name1
INNER JOIN table_name2
ON join_condition
WHERE conditions;

It is very clear from the syntax that all the JOIN clauses should be placed above the WHERE clause. The only situation when a WHERE clause appears before a JOIN is when the FROM clause is a nested query and the output of it is used as an ALIAS for the JOIN clause. More precisely, the primary query can be enclosed within parentheses in between a SELECT and FROM clause. For example:

SELECT student_name
FROM (SELECT studentId
FROM university
WHERE level = ‘Junior’)
AS j
INNER JOIN enrolled AS e
ON j.studentId = e.studentId;

Thus, we can also take note of the fact that a WHERE clause is not mandatory to be used after a JOIN clause, but if it is used, the order must be followed else the system will report an error.

MySQL is a vast subject, so here in this post I tried to address only the most commonly asked doubts by my students. I will keep on updating this post with newer questions and answers. Till then ,keep querying, keep learning!

Check similar blogs

[mc4wp_form]

Follow us
FacebooktwitterpinterestlinkedinrssyoutubevimeoinstagramFacebooktwitterpinterestlinkedinrssyoutubevimeoinstagram

Related Articles