Articles Table
Before we begin, let's introduce the articles table and its properties. This table will be our dataset containing the sample data so we will be able to filter the entities based on some criterias.
Column |
Data Type |
id |
integer |
title |
text |
author |
text |
published |
date |
views |
integer |
We've also inserted some sample data into the Articles table. This table contains both string and numeric values. So you can understand how to filter different data types in PostgreSQL.
id |
title |
author |
published |
views |
1 |
Introduction to SQL |
John Doe |
2022-05-10 |
500 |
2 |
Data Analysis Tips |
Jane Smith |
2023-01-15 |
800 |
3 |
Advanced Python |
John Doe |
2022-11-02 |
1200 |
4 |
PostgreSQL Basics |
Mark Johnson |
2023-04-20 |
300 |
5 |
Web Development 101 |
Jane Smith |
2023-02-28 |
1000 |
WHERE Clause
The WHERE statement in SQL is a fundamental clause that allows us to include only records that satisfy one or multiple conditions from the entire dataset in the query result. The basic syntax of the where clause is as follows:
```sql
SELECT column1, column2, ...
FROM table_name WHERE condition;
```
The condition part represents a boolean expression returned as true or false for each row in the table. If the condition is true, the row is included in the result set; otherwise, it is excluded. The WHERE clause supports various operators:
– Comparison Operators: These operators are used to compare values. The comparison operators in PostgreSQL include =, <> (not equal), <, >, <=, >= and BETWEEN.
– Logical Operators: These operators are used to combine multiple conditions. The logical operators in PostgreSQL include AND, OR and NOT. 4 PostgreSQL Basics Mark Johnson 2023-04-20 300 5 Web Development 101 Jane Smith 2023-02-28 1000
– Pattern Matching Operators: These operators are used to find a pattern in string values. The pattern matching operators in PostgreSQL include LIKE, ILIKE, SIMILAR TO and regular expressions (~ and ~*).
– Array Operators: PostgreSQL allows you to filter data based on array values. The array operators include = ANY, <> ALL, IN, and NOT IN.
– NULL Comparison Operators: These operators are used to check for NULL values. The NULL comparison operators in PostgreSQL include IS NULL and IS NOT NULL.
Examples of Where Clause
Let's explore some practical examples to understand how to filter data using the PostgreSQL where clause.
Basic Filtering The first example is for equality check. Consider that, you want to select posts of a specific author from the articles table, In this case your select query is as follows:
```sql
SELECT * FROM articles WHERE author = 'John Doe';
```
This query will return all articles authored by 'John Doe' as shown below
id |
title |
author |
published |
views |
1 |
Introduction to SQL |
John Doe |
2022-05-10 |
500 |
3 |
Advanced Python |
John Doe |
2022-11-02 |
1200 |
Multiple Conditional Filtering
If you want to filter data based on multiple conditions, you can use logical operators. For example, with the following query, you can get 1000+ viewed posts by John Doe from the articles table.
```sql
SELECT * FROM articles WHERE author = 'John Doe' AND views > 1000;
```
This query including multiple condition will return the following row.
id |
title |
author |
published |
views |
3 |
Advanced Python |
John Doe |
2022-11-02 |
1200 |
Pattern Matching
You can filter data based on patterns. The most popular one is LIKE operator used often with wildcard characters such as % and _ to match a pattern. The % character represents zero or more characters, while the _ character represents a single character.
For instance, if we want to retrieve articles with titles containing the word 'SQL', we can use the following query:
```sql
SELECT * FROM articles WHERE title LIKE '%SQL%';
```
Here is the result:
id |
title |
author |
published |
views |
1 |
Introduction to SQL |
John Doe |
2022-05-10 |
500 |
4 |
PostgreSQL Basics |
Mark Johnson |
2023-04-20 |
300 |
PostgreSQL also allows creating more complex patterns using the regular expressions. The ~ and ~* operators can be used in the WHERE clause to filter data based on specific patterns. Here's an example:
```sql
SELECT * FROM articles WHERE title ~ '[0-9]+';
```
This query will return all posts with the title that contains at least one digit.
id |
title |
author |
published |
views |
5 |
Web Development 101 |
Jane Smith |
2023-02-28 |
1000 |
Filtering Null Values
Null values in a dataset represents an unknown or missing value, it can behaves differently than other values when filtered. PostgreSQL have IS NULL and NULL operators to check if a column contains a NULL value. For example, to filter rows where the title column is NULL, you can use:
```sql
SELECT * FROM articles WHERE title IS NULL;
```
Subqueries
Subqueries allows building nested queries in PostgreSQL. With the help of the IN operator, we can filter the data in a table associated to other tables in the database. Since we have a single articles table, let's give a different example.
```sql SELECT *
FROM employees
WHERE department IN (SELECT department FROM departments
WHERE location = 'New York');
``
In this query, the subquery retrieves departments located in New York and the main query fetches employees who belong to those departments.
Group by and Having
The GROUP BY clause allows filtering by grouping rows of data based on one or more columns. The general syntax for the GROUP BY clause is as follows:
```sql
SELECT column1, column2, ..., FROM table
GROUP BY column1, column2, ...
```
Let's say we want to calculate the total number of views for each author. We can use the GROUP BY clause to group the rows by the author column and then use the SUM() function to calculate the total views for each author.
```sql
SELECT author, SUM(views) AS total_views FROM articles
GROUP BY author;
``
The result will show the total number of views for each author:
author |
total_views |
John Doe |
1700 |
Jane Smith |
1800 |
Mark Johnson |
300 |
The HAVING clause is used in conjunction with the GROUP BY clause to filter the result set based on a condition applied. It allows filtering the grouped data after the GROUP BY operation has been performed.
For example, let's say we want to retrieve the authors who have a total view count greater than 1000. We can use the HAVING clause to filter the results based on the aggregated total_views column
```sql
SELECT author, SUM(views) AS total_views FROM articles
GROUP BY author
HAVING SUM(views) > 1000;
```
The result will show the authors with a total view count greater than 1000:
author |
total_views |
John Doe |
1700 |
Jane Smith |
1800 |
We can also control the number of rows returned by a query and to specify the starting point for the result with the LIMIT and OFFSET clauses.
Suppose we want to retrieve the top 2 authors based on the total views of their articles, skipping the first author. Here's an example query:
```sql
SELECT author, SUM(views) AS total_views FROM articles
GROUP BY author
ORDER BY total_views DESC LIMIT 2 OFFSET 1;
```
The query will return the following result including the second and third authors based on the sorted total_views.
author |
total_views |
Jane Smith |
1800 |
John Doe |
1700 |
Conclusion
Filtering is an important operation in databases to reach a specific part of a dataset based on some criteria. For this purpose, PostgreSQL provides clauses and operators. In this post, we discussed how to filter data in a PostgreSQL database using the where, group by and having clauses.
If you have problems with integrating PostreSQL in your project, contact Sunny Security Labs - we can find a proper specialist for your project.