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.