PostgreSQL Where clause : Restricting the data – Part 1

  • Post comments:0 Comments
  • Reading time:4 mins read
  • Post category:SQL

In the previous post, we learned about projection in which we restricted the postgreSQL to display only certain columns.
Now we learn about restricting the rows using “PostgreSQL where” clause.

when retrieving data from the database, you may need to do the following.

1. restrict the rows of data that are displayed.
2. specify the order in which the rows are displayed.

PostgreSQL where clause:

You can restrict the rows that are returned from the query by using the PostgreSQL where clause. A where clause contains a condition that must be met and it directly follows the from clause. If the condition is true, the row meeting the condition is returned.

Syntax:

SELECT {column, column, …}
FROM {table}
WHERE {condition};

The SQL where clause can compare values in columns, literal, arithmetic expressions or functions. It consists of three elements

1. column name
2. comparison condition
3. column name, constant or list of values.

PostgreSQL where clause example

Here, In the above table, if we want to restrict the emp list whose deptno is 20, we write the query as

select * from emp
            where deptno=20;

We can also do projection by selecting column list.

e.g.,

PostgreSQL where like:

Use the LIKE operator to perform wildcard searches of valid search string values. Search conditions can contain either literal characters or numbers.

The following searches can be used.

1. % denotes zero or many characters, denotes one character.
2. _ denotes one character.

Here, though we have two names starting with ‘adam’, we got only one result which is ‘adams’ and ‘adamsmith’ is ignored.

PostgreSQL where not null/null:

A field with a NULL value is a field with no value.

If a field in a table is optional, it is possible to insert a new record or update a record without adding a value to this field. Then, the field will be saved with a NULL value.

It is very important to understand that a NULL value is different from a zero value or a field that contains spaces. A field with a NULL value is one that has been left blank during record creation!

In order to find the rows where the value of a column is or is not NULL, you would use IS NULL or IS NOT NULL.

Example.,

Words from postgreshelp

Thank you for giving your valuable time to read the above information. I hope the content served your purpose in reaching out the blog.
Suggestions for improvement of the blog are highly appreciable. Please contact us for any information/suggestion/feedback.

If you want to be updated with all our articles

please follow us on Facebook Twitter
Please subscribe to our newsletter.

 

Leave a Reply