Restricting and Sorting Data

In order to customize your data selection, you might be needing to restrict / filter out some data and sort / order them. To do so, you need to use WHERE and ORDER BY clauses.

Let’s imagine a table with employees called EMPLOYEE

id_employee	name_full	id_department
1	John Doe	10
2	Marry Jane	30
3	Frank Dux	20
4	Jane Black	10

To get all employees from this table you simple perform

SELECT * FROM employee;

To get only employees from with id_department 10 you need to add WHERE condition.

SELECT * 
FROM employee
WHERE id_department = 10;

You might need to implement more than one condition and you can easily do so by using other operators.

SELECT * 
FROM employee
WHERE id_department = 10
AND id_employee >= 3;

You can filter all columns presented in a table and they do not even be in the selection.

SELECT name_full
FROM employee
WHERE 1=1
AND id_department = 10
AND id_employee >= 3;

As you can see I have added this ‘1=1’ condition. What does it do? It is my best practice and helps a lot during debugging and testing results with different conditions. It has no purpose because it is always evaluated as TRUE but it allows you to easily comment or un-comment a condition. If you omitted this and commented the first condition out you would need to delete the AND/OR operator accordingly as well – which might be bothering after a while.

As mentioned in precedence operators you can combine parentheses and define the order of execution. I recommend you to stick with a proper indentation and code formatting throughout your code (not only in WHERE clause).

SELECT id_employee
, name_full
FROM employee
WHERE 1=1
AND (
       id_employee IN (1,2)
        OR id_employee BETWEEN 4 AND 6
    )
AND id_department = 30
AND (
     name_full LIKE 'J%'
      OR 
      (
       name_full = 'Marry' 
        AND id_department >= 0
      )
    )

I know some people might say it wastes the space and is not easy on the eye … however, I guess it is more readable than this one where it is easy to get lost in precedence

SELECT id_employee
, name_full
FROM employee
WHERE 1=1
AND (id_employee IN (1,2) 
OR id_employee BETWEEN 4 AND 6)
AND id_department = 30
AND ( name_full LIKE 'J%' 
OR ( name_full = 'Marry'  
AND id_department >= 0 )
    )