The terms FILTER and WHERE are used in PostgreSQL to further refine data selection, but they have different purposes and can be used in different contexts. Let\u2019s explore both keywords in detail, understanding their unique use cases with code examples.<\/p>\n
Creating a database is time-consuming but with Docker<\/a>, it becomes easy. To understand how to install Docker on Windows OS, you can view the video provided at this link<\/a>. After completing the task, launch the terminal and execute the command below to configure and start postgresql.<\/p>\n Don’t forget to input your preferred password. Assuming everything goes smoothly, the postgresql database server will be operational on port number 5432, allowing you to connect using the Dbeaver<\/a> GUI tool.<\/p>\n The Consider a table named To find out the employees whose age is greater than 30, we can use the following The Using the These days most people focus on using such queries in application development. Here is a snippet of how the above SQL queries can be used in Spring Java application development services<\/a>.<\/p>\n In this example, we have created a Spring dao interface and used the native SQL approach to do the query operations.<\/p>\n The PostgreSQL keywords -- Remember to change the password \u2013\ndocker run -d -p 5432:5432 -e POSTGRES_PASSWORD=your_password --name postgres postgres\n-- command to stop the Postgres docker container --\ndocker stop postgres\n-- command to remove the Postgres docker container --\ndocker rm postgres\n<\/pre>\n
<\/a>2. The WHERE Keyword<\/h2>\n
WHERE<\/code> keyword filters table rows based on specified conditions. It is a crucial clause in SQL that is used in SELECT<\/code>, UPDATE<\/code>, DELETE<\/code>, and other SQL statements to specify the rows affected by the query. The format for this clause is the following:<\/p>\nSELECT column1, column2, ... FROM table_name WHERE condition;\n<\/pre>\n
2.1 Example<\/h3>\n
employees<\/code> with the following structure:<\/p>\nCREATE TABLE employees (\n id SERIAL PRIMARY KEY, \n name VARCHAR(100), \n age INT, \n department VARCHAR(50)\n);\n<\/pre>\n
WHERE<\/code> clause:<\/p>\nSELECT \n * \nFROM \n employees \nWHERE \n age > 30;\n<\/pre>\n
2.2 Code Breakdown<\/h3>\n
\n
SELECT * FROM employees<\/code>: This query selects all columns from the employees<\/code> table.<\/li>\nWHERE age > 30<\/code>: This filter in the select query fetches only those rows where the age<\/code> column is greater than 30.<\/li>\n<\/ul>\n<\/a>3. The FILTER Keyword<\/h2>\n
FILTER<\/code> keyword has a role in aggregate functions as it filters rows before the function is executed. It is commonly used in combination with GROUP BY<\/code> clause to compute aggregates that meet specific criteria. The syntax is shown in the following manner:<\/p>\nSELECT aggregate_function(column) FROM table_name WHERE condition FILTER (WHERE filter_condition);\n<\/pre>\n
3.1 Example<\/h3>\n
employees<\/code> table created above, we will find the average age of employees who work in the ‘IT’ department:<\/p>\nSELECT \n AVG(age) FILTER (\n WHERE \n department = 'IT'\n ) AS avg_age_it \nFROM \n employees;\n<\/pre>\n
3.2 Code Breakdown<\/h3>\n
\n
SELECT AVG(age)<\/code>: This keyword calculates the average of the age<\/code> column.<\/li>\nFILTER (WHERE department = 'IT')<\/code>: This filters in the query fetches those rows where the department<\/code> is ‘IT’ before calculating the average.<\/li>\nAS avg_age_it<\/code>: This keyword gives an alias name to the resulting column, avg_age_it<\/code>.<\/li>\n<\/ul>\n<\/a>4. Using in Application Development<\/h2>\n
import org.springframework.data.jpa.repository.JpaRepository;\nimport org.springframework.data.jpa.repository.Query;\nimport org.springframework.stereotype.Repository;\nimport java.util.List;\n@Repository\npublic interface EmployeesDao extends JpaRepository<Employee, Integer> {\n @Query(nativeQuery = true, value = \"SELECT * FROM employees WHERE age > ?1\")\n List<Employee> getEmployeesByAge(int age);\n @Query(nativeQuery = true, value = \"SELECT AVG(age) FILTER (WHERE department = 'IT') AS avg_age_it FROM employees;\")\n int getAverageAgeIT();\n}\n<\/pre>\n<\/a>5. Comparison and Conclusion<\/h2>\n
WHERE<\/code> and FILTER<\/code> play important roles in data querying, yet they fulfill different functions:<\/p>\n\n
WHERE<\/code> clause is used to narrow down rows in standard queries, affecting which rows are selected or manipulated<\/li>\nFILTER<\/code> clause is used with aggregate functions to define conditions that determine which rows are considered in the aggregate calculation.<\/li>\n<\/ul>\n