Summary: in this tutorial, you will learn how to use the PostgreSQL IN operator in the WHERE clause to check if a value matches any value in a list. Show PostgreSQL IN operator syntaxYou use IN operator in the WHERE clause to check if a value matches any value in a list of values. The syntax of the IN operator is as follows: value IN (value1,value2,...) Code language: SQL (Structured Query Language) (sql)The IN operator returns true if the value matches any value in the list i.e., value1 , value2 , … The list of values can be a list of literal values such as numbers, strings or a result of a SELECT statement like this: value IN (SELECT column_name FROM table_name); Code language: SQL (Structured Query Language) (sql)The query inside the parentheses is called a subquery, which is a query nested inside another query. Note that you will learn more about the subquery in the subsequent tutorial PostgreSQL IN operator examplesSuppose you want to know the rental information of customer id 1 and 2, you can use the IN operator in the WHERE clause as follows: SELECT customer_id, rental_id, return_date FROM rental WHERE customer_id IN (1, 2) ORDER BY return_date DESC; Code language: SQL (Structured Query Language) (sql) The following query uses the equal (=) and OR operators instead of the IN operator. It is equivalent to the query above: SELECT rental_id, customer_id, return_date FROM rental WHERE customer_id = 1 OR customer_id = 2 ORDER BY return_date DESC; Code language: SQL (Structured Query Language) (sql)The query that uses the IN operator is shorter and more readable than the query that uses equal (=) and OR operators. In addition, PostgreSQL executes the query with the IN operator much faster than the same query that uses a list of OR operators. PostgreSQL NOT IN operatorYou can combine the IN operator with the NOT operator to select rows whose values do not match the values in the list. For example, the following statement finds all rentals with the customer id is not 1 or 2. SELECT customer_id, rental_id, return_date FROM rental WHERE customer_id NOT IN (1, 2); Code language: SQL (Structured Query Language) (sql)Similar to the IN operator, you can use the not equal (<>) and AND operators to write the NOT IN operator: SELECT customer_id, rental_id, return_date FROM rental WHERE customer_id <> 1 AND customer_id <> 2; This query returns the same output as above query that use the NOT IN operator. PostgreSQL IN with a subqueryThe following query returns a list of customer ids from the rental table with the return date is 2005-05-27: SELECT customer_id FROM rental WHERE CAST (return_date AS DATE) = '2005-05-27' ORDER BY customer_id; Code language: SQL (Structured Query Language) (sql)Because this query returns a list of values, you can use it as the input of the IN operator like this: SELECT customer_id, first_name, last_name FROM customer WHERE customer_id IN ( SELECT customer_id FROM rental WHERE CAST (return_date AS DATE) = '2005-05-27' ) ORDER BY customer_id; Code language: SQL (Structured Query Language) (sql)For more information on the subquery, check it out the subquery tutorial. In this tutorial, you have learned how to use the PostgreSQL IN operator to check if a value matches any value in a list of values. Was this tutorial helpful ?
This SQL tutorial explores all of the comparison operators used in SQL to test for equality and inequality, as well as the more advanced operators.
Comparison operators are used in the WHERE clause to determine which records to select. Here is a list of the comparison operators that you can use in SQL:
If you want to follow along with this tutorial, get the DDL to create the tables and the DML to populate the data. Then try the examples in your own database! Get DDL/DML
In SQL, you can use the = operator to test for equality in a query. In this example, we have a table called suppliers with the following data:
Enter the following SQL statement: Try It SELECT * FROM suppliers WHERE supplier_name = 'Microsoft';There will be 1 record selected. These are the results that you should see:
In this example, the SELECT statement above would return all rows from the suppliers table where the supplier_name is equal to Microsoft.
In SQL, there are two ways to test for inequality in a query. You can use either the <> or != operator. Both will return the same results. Let's use the same suppliers table as the previous example.
Enter the following SQL statement to test for inequality using the <> operator: Try It SELECT * FROM suppliers WHERE supplier_name <> 'Microsoft';OR enter this next SQL statement to use the != operator: Try It SELECT * FROM suppliers WHERE supplier_name != 'Microsoft'; There will be 8 records selected. These are the results you should see with either one of the SQL statements:
In the example, both SELECT statements would return all rows from the suppliers table where the supplier_name is not equal to Microsoft.
You can use the > operator in SQL to test for an expression greater than. In this example, we have a table called customers with the following data:
Enter the following SQL statement: Try It SELECT * FROM customers WHERE customer_id > 6000;There will be 3 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than 6000. A customer_id equal to 6000 would not be included in the result set.
In SQL, you can use the >= operator to test for an expression greater than or equal to. Let's use the same customers table as the previous example.
Enter the following SQL statement: Try It SELECT * FROM customers WHERE customer_id >= 6000;There will be 4 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the customers table where the customer_id is greater than or equal to 6000. In this case, the supplier_id equal to 6000 would be included in the result set.
You can use the < operator in SQL to test for an expression less than. In this example, we have a table called products with the following data:
Enter the following SQL statement: Try It SELECT * FROM products WHERE product_id < 5;There will be 4 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the products table where the product_id is less than 5. A product_id equal to 5 would not be included in the result set.
In SQL, you can use the <= operator to test for an expression less than or equal to. Let's use the same products table as the previous example.
Enter the following SQL statement: Try It SELECT * FROM products WHERE product_id <= 5;There will be 5 records selected. These are the results that you should see:
In this example, the SELECT statement would return all rows from the products table where the product_id is less than or equal to 5. In this case, the product_id equal to 5 would be included in the result set.
To learn more about the advanced comparison operators, we've written tutorials to discuss each one individually. These topics will be covered later, or you can jump to one of these tutorials now.
|
Which operator is used with WHERE to check whether a value matches any value within a given list?
zusammenhängende Posts
Urheberrechte © © 2024 wiewird Inc.
