Dev Learning Lab

The labs on this site need JavaScript to run. The same content is written out below.

Fundamentals › Database

WHERE

What does WHERE decide, and what happens without it?

WHERE is the "WHERE condition" that you add after an SQL statement. It decides which of the rows in a table an UPDATE, DELETE, or SELECT applies to. The database looks at the rows one by one, checks whether the condition is met, and applies the statement to, or reads, only the rows that match. In this lab you change the kind of statement and the WHERE condition and run the same SQL on the same table each time. You see how many rows the statement applies to and which rows are left at the end. You also see what happens when you remove WHERE.

Checked against real execution · Ran all 12 combinations on H2 2.4.240 and checked against the results, and checked the rules against the MySQL documentation

Concepts in this lab

WHERE clause
The WHERE clause is a condition that you add after a SELECT, UPDATE, or DELETE statement to decide which rows of a table the statement applies to. The database checks whether the condition is true for each row, and it applies the UPDATE or DELETE to, or reads with SELECT, only the rows for which it is true. If several rows match, the statement is applied to all of them. If no row matches, it is applied to no row, and that is not an error. If you do not write WHERE, every row is a target, so SELECT reads everything, UPDATE changes every row, and DELETE deletes every row.
UPDATE and DELETE
UPDATE is a statement that changes the values in the columns of rows that are already in a table, and DELETE is a statement that deletes rows. Both decide which rows they apply to with a WHERE condition, and when they run, they return the number of rows they applied to (in JDBC, executeUpdate returns that number). If you do not write WHERE, UPDATE changes every row and DELETE deletes every row. Even then the statement finishes without an error, so if you leave out WHERE in a statement that was meant to change or delete one person, the database does not warn you. Even if DELETE deletes all the rows, the table itself remains.
Rows and columns
A table in a relational database is made of columns and rows, like a spreadsheet. A column is a kind of cell whose name and type are fixed when the table is created. For example, id and name are the columns of the member table. A row is one line filled with values for those columns, and the information about one member is one row. INSERT adds one row, UPDATE changes every row that matches the WHERE condition, and SELECT reads rows. A column with no value is shown as NULL.

Try these

Running with the default settings

  1. CREATE TABLE and INSERT: The database creates the member table and adds three rows (Alice, Bob, and Carol). A table is made of columns (id, name, age) and rows (one line of information about one person). These three rows are all the rows that the statement below can apply to.
  2. UPDATE member SET name = 'Withdrawn' WHERE id = 2: This statement has a WHERE condition (WHERE id = 2). The condition after WHERE decides "which rows to apply this statement to." The database looks at the rows of the member table one by one and checks whether id = 2 is true. We did not write the code that does this check. The database does it by itself for every statement.
  3. The database looks at the first row (1, Alice, 20): Is id = 2? The id of this row is 1, not 2, so the answer is "No." This row does not match the condition, so the statement is not applied to it.
  4. The second row (2, Bob, 30): Is id = 2? The id of this row is 2, so the answer is "Yes." This row matches the condition.
  5. The third row (3, Carol, 30): Is id = 2? The id of this row is 3, not 2, so the answer is "No." This row does not match the condition, so the statement is not applied to it.
  6. UPDATE member SET name = 'Withdrawn' WHERE id = 2: The statement is applied only to the rows that matched (id 2). The database changes the name of that row to Withdrawn. The value it returns (the number of rows changed) is 1. The rows that did not match (id 1, 3) stay as they are.
  7. SELECT * FROM member ORDER BY id: This SELECT has no WHERE, so it reads all the rows left in the table. The result is (1, Alice, 20), (2, Withdrawn, 30), (3, Carol, 30). The names in the rows that matched (id 2) were changed to Withdrawn, and the rest are as they were.

Result of this run

Code

CREATE TABLE member (
    id   INT PRIMARY KEY,
    name VARCHAR(20),
    age  INT
);
INSERT INTO member VALUES (1, 'Alice', 20);
INSERT INTO member VALUES (2, 'Bob', 30);
INSERT INTO member VALUES (3, 'Carol', 30);

-- WHERE: apply this statement only to the rows that match the condition
UPDATE member SET name = 'Withdrawn' WHERE id = 2;
SELECT * FROM member ORDER BY id;

Assumptions of this model

The model was checked against real execution on H2 2.4.240 (an in-memory database, through JDBC). For all 12 combinations, the whole SQL on the screen (including the comment line) was run character by character, one statement at a time, and the following were checked against the model: the number of rows the statement applied to and which rows they were (the rows whose name changed for UPDATE, the rows that disappeared for DELETE, and the rows that were read for SELECT), whether an error occurred, and the result of the last SELECT. That a condition that matches no row (WHERE id = 9) and an UPDATE or DELETE without WHERE finish without an error on H2, and that the table remains after a DELETE without WHERE so that the last SELECT returns an empty result, were checked in this execution. I opened the MySQL 8.4 documentation directly and checked the rules in the UPDATE statement (section 15.2.17: with WHERE, only the rows that match the condition are updated, and without it all rows are updated), the DELETE statement (section 15.2.2: without WHERE, all rows are deleted), and the SELECT statement (section 15.2.13: without WHERE, all rows are selected). I also checked in the MySQL documentation (Using Safe-Updates Mode in the tips for the mysql client) that MySQL has a safe-updates mode that prevents an UPDATE or DELETE where WHERE was forgotten. When this mode is on, an UPDATE or DELETE whose WHERE has no key condition gives an error, and this lab does not cover that mode. The check by execution on MySQL was not done. So the rule above rests on the documentation and the H2 execution agreeing, and it was not verified by execution whether MySQL gives the same result for the same SQL. The screen is drawn as the database looking at the rows one by one and checking the condition. A real database may find the matching rows in another way, depending on the implementation, for example by using an index. What was checked by execution is which rows the statement was applied to, not the order of checking. The "number of rows applied to" that a statement returns was the same as the number of changed rows in this lab, because the names of all the matched rows were different from before in the UPDATE. How a database counts a row whose new value is already the same can differ with the database and the driver settings, so this lab does not cover it. The condition is only one equality (=) comparison. AND and OR, ranges, LIKE, and comparisons with NULL are not covered.

Previous lab: Foreign keys · Next lab: Joins

Concepts in this lab

What this lab shows

Settings

Code


      
    


    

      
Diagram

      

      

    


    

      

      

      

    


  


  

    

      
Run comparison

      

      

    

    

      
Try these