🇯🇵 日本語 | 🇺🇸 English | 🇪🇸 Español | 🇵🇹 Português | 🇹🇭 ไทย | 🇨🇳 中文

[SQL for Beginners] Become a Master of Multiple Conditions! Fully Master AND, OR, & NOT

"I want to get specific data from the database, but one condition just isn't enough..."
As a web creator, have you ever hit a wall like this while trying to level up your skills?

SQL is the language used to manipulate databases, and the SELECT statement, which extracts data, is the absolute basic. However, to get the exact information you truly want, the technique of combining multiple conditions to filter data is essential.

In this article, we'll thoroughly explain how to use the three logical operators for specifying multiple conditions—AND, OR, and NOT—with sample code that even beginners can copy and paste to get working results! By experiencing that "it works!" moment, you'll come to understand how fun and convenient SQL can be.

Preparation: Let's Set Up a Practice Table

First, let's create the practice table we'll use in this article. It's an employees table that stores fictional employee information. Please copy the following SQL statement and run it in your own database environment.

But don't worry if you don't have an environment ready to go! Later in the article, we provide an "SQL execution environment" that lets you try SQL right in your browser, so feel free to just read on for now. 


-- Drop table if it exists
DROP TABLE IF EXISTS employees;

-- Create the employees table
CREATE TABLE employees (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(50),
    position VARCHAR(50),
    salary INT,
    hire_date DATE
);

-- Insert the data
INSERT INTO employees (id, name, department, position, salary, hire_date) VALUES
(1, '山田 太郎', '営業部', 'マネージャー', 600000, '2018-04-01'),
(2, '鈴木 花子', '開発部', 'エンジニア', 550000, '2020-10-01'),
(3, '佐藤 次郎', '営業部', 'スタッフ', 400000, '2022-04-01'),
(4, '高橋 三郎', '人事部', 'マネージャー', 650000, '2017-07-01'),
(5, '田中 恵子', '開発部', 'デザイナー', 500000, '2021-08-15'),
(6, '伊藤 四郎', '営業部', 'マネージャー', 700000, '2015-02-01'),
(7, '渡辺 久美', '開発部', 'エンジニア', 750000, '2019-06-01'),
(8, '山本 五郎', '人事部', 'スタッフ', 380000, '2023-04-01'),
(9, '中村 さくら', '開発部', 'マネージャー', 800000, '2016-09-01'),
(10, '小林 健太', '営業部', 'スタッフ', 420000, '2023-10-01');

All set? Let's dive into the world of conditional queries using this table!

1. AND: Pinpoint Data That Meets All Conditions!

AND means that a record must meet all specified conditions. It's used when you want to extract only the data that satisfies every single requirement.

For example, a case where you want employee information for someone who is "in the Sales department" and "has the position of Manager."

Basic Usage

In the WHERE clause, you connect conditions using AND. 


SELECT *
FROM employees
WHERE department = '営業部' AND position = 'マネージャー';

When you run this SQL, you'll get all the data for employees whose department is '営業部' (Sales) and whose position is 'マネージャー' (Manager). You'll see that Mr. Yamada and Mr. Ito match.

Chaining Multiple ANDs

You can chain as many AND conditions as you like. The more conditions you add, the more precisely your data will be filtered.

As an example, let's find an employee who is "in the Sales department" and "is a Manager" and "has a salary of 650,000 or more." 


SELECT *
FROM employees
WHERE
    department = '営業部'
    AND position = 'マネージャー'
    AND salary >= 650000;

This time, only Mr. Ito was returned. By adding another condition, Mr. Yamada was excluded. As you can see, AND is extremely powerful for pinpointing specific data by layering conditions.

2. OR: Broadly Get Data That Meets Any Condition!

OR means that a record must meet at least one of the specified conditions. It's used when you want to extract data that satisfies any of the requirements.

For instance, it's perfect for when you want a list of employees who are "in the Sales department" or "in the Development department."

Basic Usage

Just like AND, you connect conditions with OR in the WHERE clause. 


SELECT *
FROM employees
WHERE department = '営業部' OR department = '開発部';

Running this will display all employees from the '営業部' (Sales) department and all employees from the '開発部' (Development) department. While AND narrows down the results by layering conditions, you can think of OR as adding more and more possibilities to the result set.

Chaining Multiple ORs

Of course, you can chain multiple ORs as well. Let's find employees who are "a Manager" or "have a salary of 700,000 or more." 


SELECT *
FROM employees
WHERE position = 'マネージャー' OR salary >= 700000;

This result includes:

Mr. Ito and Ms. Nakamura meet both conditions, and they are of course included in the results. OR is a flexible condition that means "it's OK as long as it meets at least one."

3. NOT: Extract Data That Doesn't Match a Condition!

As its name suggests, NOT specifies a negative condition: "is not ~". It's useful when you want to exclude data with a certain characteristic.

Basic Usage

Place NOT directly before the condition you want to negate in the WHERE clause.

As an example, let's get all employees who are not in the '開発部' (Development) department. 


SELECT *
FROM employees
WHERE NOT department = '開発部';

Running this will display employees from all departments except for '開発部' (Development)—in other words, employees from '営業部' (Sales) and '人事部' (HR).
By the way, you can get the same result by writing WHERE department != '開発部' or WHERE department <> '開発部'. Which one you use is a matter of preference or team standards, but NOT is unique in that it can be combined with other operators like IN and EXISTS, giving you a wider range of expression.

[Advanced] Combining AND & OR and the Importance of Parentheses

Okay, here's where things get real! By combining AND and OR, you can specify even more complex conditions. However, there's a huge pitfall here that beginners often fall into. That pitfall is operator precedence.

In SQL, if you don't specify otherwise, AND is evaluated before OR. This is similar to how in math, multiplication (×) is done before addition (+).

To get the data you actually intend to get, it's extremely important to use () (parentheses) to clearly define the order of operations, just like in math.

Bad Example: Employees who are in the "Sales" or "Development" department AND are "Managers"?

If you were to write this condition without thinking too hard, you might end up with this: 


-- This SQL gives an unintended result
SELECT *
FROM employees
WHERE
    department = '営業部'
    OR department = '開発部' AND position = 'マネージャー';

What do you think will happen if you run this SQL?
Because AND has higher precedence, the database first evaluates department = '開発部' AND position = 'マネージャー' (Managers in the Development department). Then, it takes that result and connects it with department = '営業部' (all employees in the Sales department) using OR.

In other words, what this query is actually looking for is:

It's looking for records that match either of those. This isn't what we originally wanted, which was "Managers who are in either the Sales or Development department," right?

Good Example: Grouping Correctly with Parentheses

To correctly express the condition "(in the Sales department OR the Development department) AND has the position of Manager," you need to wrap the OR part in (). 


-- Using parentheses to get the intended result!
SELECT *
FROM employees
WHERE
    (department = '営業部' OR department = '開発部')
    AND position = 'マネージャー';

By doing this, (department = '営業部' OR department = '開発部') is evaluated first, becoming a single block of logic that means "belongs to either the Sales or Development department." The AND position = 'マネージャー' condition is then applied to that result, allowing you to correctly extract "Managers who are in either the Sales or Development department."

When mixing AND and OR, always use parentheses () to explicitly define the order of precedence! Remember this as an ironclad rule!

Practice! Let's Run SQL in Your Browser

Thanks for your patience! Here's a gift for you: an environment where you can try out everything you've learned so far by getting your hands dirty.

This uses a JavaScript library (AlaSQL.js) to allow you to run SQL right here in your browser. Copy all of the code below, save it as a file like sql_practice.html, and open it in your browser.

Paste the various SQL queries introduced in this article into the text area and press the "Execute" button, and you'll see the results on the spot. Try out different things and get a feel for how the data changes! 


<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <title>SQL Playground</title>
    <script src="https://cdn.jsdelivr.net/npm/alasql@4"></script>
    <style>
        body { font-family: sans-serif; padding: 2rem; background-color: #f9f9f9; }
        .container { max-width: 800px; margin: auto; background: white; padding: 2rem; border-radius: 8px; box-shadow: 0 4px 8px rgba(0,0,0,0.1); }
        h1 { color: #333; }
        textarea { width: 100%; height: 150px; font-family: monospace; font-size: 16px; padding: 10px; border: 1px solid #ccc; border-radius: 4px; box-sizing: border-box; margin-bottom: 1rem; }
        button { background-color: #007bff; color: white; padding: 10px 20px; border: none; border-radius: 4px; cursor: pointer; font-size: 16px; }
        button:hover { background-color: #0056b3; }
        #result-area { margin-top: 2rem; }
        table { width: 100%; border-collapse: collapse; margin-top: 1rem; }
        th, td { border: 1px solid #ddd; padding: 8px; text-align: left; }
        th { background-color: #f2f2f2; }
        .error { color: red; font-weight: bold; }
    </style>
</head>
<body>
    <div class="container">
        <h1>Let's Try Some SQL!</h1>
        <p>Enter an SQL statement in the text area below and press the "Execute SQL" button. Try out the different SQL queries we introduced in the article!</p>
        <textarea id="sql-input">-- Write your SQL in here
SELECT * FROM employees WHERE (department = '営業部' OR department = '開発部') AND position = 'マネージャー';</textarea>
        <button onclick="executeSQL()">Execute SQL</button>
        <div id="result-area"></div>
    </div>

    <script>
        // Prepare the data
        const createTableSQL = `
            CREATE TABLE employees (
                id INT, name STRING, department STRING, position STRING, salary INT, hire_date DATE
            );
        `;
        const insertDataSQL = `
            INSERT INTO employees VALUES
            (1, '山田 太郎', '営業部', 'マネージャー', 600000, '2018-04-01'),
            (2, '鈴木 花子', '開発部', 'エンジニア', 550000, '2020-10-01'),
            (3, '佐藤 次郎', '営業部', 'スタッフ', 400000, '2022-04-01'),
            (4, '高橋 三郎', '人事部', 'マネージャー', 650000, '2017-07-01'),
            (5, '田中 恵子', '開発部', 'デザイナー', 500000, '2021-08-15'),
            (6, '伊藤 四郎', '営業部', 'マネージャー', 700000, '2015-02-01'),
            (7, '渡辺 久美', '開発部', 'エンジニア', 750000, '2019-06-01'),
            (8, '山本 五郎', '人事部', 'スタッフ', 380000, '2023-04-01'),
            (9, '中村 さくら', '開発部', 'マネージャー', 800000, '2016-09-01'),
            (10, '小林 健太', '営業部', 'スタッフ', 420000, '2023-10-01');
        `;

        // Initialize AlaSQL database
        const db = new alasql.Database();
        db.exec(createTableSQL);
        db.exec(insertDataSQL);

        function executeSQL() {
            const sql = document.getElementById('sql-input').value;
            const resultArea = document.getElementById('result-area');
            resultArea.innerHTML = ''; // Clear previous results

            try {
                const result = db.exec(sql);
                if (result.length > 0) {
                    resultArea.appendChild(createTable(result));
                } else {
                    resultArea.innerHTML = '<p>The query returned 0 rows.</p>';
                }
            } catch (e) {
                resultArea.innerHTML = `<p class="error">Error: ${e.message}</p>`;
            }
        }

        function createTable(data) {
            const table = document.createElement('table');
            const thead = table.createTHead();
            const tbody = table.createTBody();
            const headerRow = thead.insertRow();

            // Create headers
            for (const key in data[0]) {
                const th = document.createElement('th');
                th.textContent = key;
                headerRow.appendChild(th);
            }

            // Create data rows
            data.forEach(rowData => {
                const row = tbody.insertRow();
                for (const key in rowData) {
                    const cell = row.insertCell();
                    cell.textContent = rowData[key];
                }
            });
            return table;
        }

        // Initial display
        executeSQL();
    </script>
</body>
</html>

Points to Watch Out For and Related Techniques

Finally, let's introduce a few points to be careful about when dealing with multiple conditions, as well as some related and useful operators.

Handling NULL

Databases have a special value called NULL. It represents an empty state, indicating that "no data exists."
The important thing to note is that you cannot compare NULL using = or !=. To check if something is NULL, you must use the dedicated IS NULL or IS NOT NULL operators. 


-- Find employees where salary is not set (is NULL)
SELECT * FROM employees WHERE salary IS NULL;

-- Find employees where salary is set (is NOT NULL)
SELECT * FROM employees WHERE salary IS NOT NULL;

Our sample data doesn't contain any NULL values, but you'll encounter them frequently in real-world development. Writing WHERE salary = NULL won't cause an error, but it also won't give you the results you intend, so be careful.

More Convenient Conditions: IN and BETWEEN

Sometimes, you can write your queries more simply instead of using many ORs or ANDs.

IN: An Alternative to OR

When you're connecting multiple values for the same column with OR, like "in the Sales, HR, or Accounting department," you can write it more cleanly using IN. 


-- The verbose way with OR
SELECT * FROM employees
WHERE department = '営業部' OR department = '人事部';

-- The clean way with IN
SELECT * FROM employees
WHERE department IN ('営業部', '人事部');

BETWEEN: An Alternative to AND

When you need to specify a range of numbers or dates, like "salary is greater than or equal to 400,000 and less than or equal to 600,000," the BETWEEN operator is convenient. 


-- Specifying a range with AND
SELECT * FROM employees
WHERE salary >= 400000 AND salary <= 600000;

-- The clean way with BETWEEN
SELECT * FROM employees
WHERE salary BETWEEN 400000 AND 600000;

You can, of course, combine these operators with AND, OR, and NOT as well. Adding them to your toolkit will enable you to write even smarter SQL.

Summary

Great work! In this article, we learned about the fundamentals of specifying multiple conditions in SQL—AND, OR, and NOT—using concrete examples.

By mastering these logical operators, you'll be able to extract data just the way you want, even with complex conditions, and your data manipulation skills will improve dramatically.

Please use the SQL execution environment we provided to try out various conditions. With SQL, the more you write and the more you try, the better you'll get. We hope this article serves as a helpful step on your journey to becoming an SQL master!

Related Templates