Select rows with even or odd values in SQL

Syntax

To find and return the records with the odd or even values, the most simple way is to check the remainder when we divide the column value by 2. When the remainder is 0, that’s an even number, otherwise, that’s an odd number.

Here is the syntax:

In PostgreSQL, My SQL, and Oracle, we use MOD function to check the remainder.

To find rows where a specified column has even values:

SELECT * 
FROM table_name 
WHERE mod(column_name,2) = 0;

To find rows where a specified column has odd values:

SELECT * 
FROM table_name 
WHERE mod(column_name,2) <> 0;

In MS SQL Server, there is no MOD function and you can use %.

To find rows where a specified column has even values:

SELECT *
FROM table_name 
where column_name % 2 = 0;

To find rows where a specified column has odd values:

SELECT *
FROM table_name 
where column_name % 2 <> 0;

Example

We have table dept_manager in MySQL:

emp_id	dept_id	from_date	to_date
110022	d001	1985-01-01	1991-10-01
110039	d001	1991-10-01	9999-01-01
110085	d002	1985-01-01	1989-12-17
110114	d002	1989-12-17	9999-01-01
110183	d003	1985-01-01	1992-03-21
110228	d003	1992-03-21	9999-01-01
110303	d004	1985-01-01	1988-09-09
110344	d004	1988-09-09	1992-08-02
110386	d004	1992-08-02	1996-08-30
110420	d004	1996-08-30	9999-01-01
110511	d005	1985-01-01	1992-04-25
110567	d005	1992-04-25	9999-01-01
110725	d006	1985-01-01	1989-05-06
110765	d006	1989-05-06	1991-09-12
110800	d006	1991-09-12	1994-06-28
110854	d006	1994-06-28	9999-01-01
111035	d007	1985-01-01	1991-03-07
111133	d007	1991-03-07	9999-01-01
111400	d008	1985-01-01	1991-04-08
111534	d008	1991-04-08	9999-01-01
111692	d009	1985-01-01	1988-10-17
111784	d009	1988-10-17	1992-09-08
111877	d009	1992-09-08	1996-01-03
111939	d009	1996-01-03	9999-01-01

Now find all the rows having emp_id as even number:

SELECT *
FROM dept_manager
WHERE MOD(emp_id, 2) = 0;

The results is:

emp_id	dept_id	from_date	to_date
110022	d001	1985-01-01	1991-10-01
110114	d002	1989-12-17	9999-01-01
110228	d003	1992-03-21	9999-01-01
110344	d004	1988-09-09	1992-08-02
110386	d004	1992-08-02	1996-08-30
110420	d004	1996-08-30	9999-01-01
110800	d006	1991-09-12	1994-06-28
110854	d006	1994-06-28	9999-01-01
111400	d008	1985-01-01	1991-04-08
111534	d008	1991-04-08	9999-01-01
111692	d009	1985-01-01	1988-10-17
111784	d009	1988-10-17	1992-09-08

Need a good GUI tool for databases? TablePlus provides a native client that allows you to access and manage Oracle, MySQL, SQL Server, PostgreSQL, and many other databases simultaneously using an intuitive and powerful graphical interface.

TablePlus