SQL operatorių pamoka - bitų, palyginimo, aritmetikos ir loginių operatorių užklausų pavyzdžiai

Iš esmės internetas ir visos jo programos yra tik duomenys.

Kiekvienas el. Paštas, „Twitter“, asmenukė, banko operacija ir dar daugiau yra tik duomenys, sėdintys kažkur duomenų bazėje.

Kad šie duomenys būtų naudingi, turime mokėti juos gauti. Tačiau nepakanka tik gauti duomenis - duomenys taip pat turi būti naudingi ir tinkami mūsų situacijai.

Duomenų bazės lygmeniu mes prašome konkrečios informacijos iš duomenų bazės, rašydami SQL užklausą. Ši SQL užklausa nurodo duomenis, kuriuos norime gauti, ir formatą, kuriuo norime juos gauti.

Šiame straipsnyje mes apžvelgsime visus dažniausiai naudojamus SQL užklausos filtravimo būdus.

Štai ką aptarsime:

  • Duomenų bazės nustatymas
  • Vartotojų kūrimas
  • Įterpiami vartotojai
  • Duomenų filtravimas naudojant WHERE
  • Loginiai operatoriai ( AND/ OR/ NOT)
  • Palyginimas operatoriai ( <, >, <=, >=)
  • Aritmetiniai operatoriai ( +, -, *, /, %)
  • Esami operatoriai ( IN/ NOT IN)
  • Dalinis derinimas naudojant LIKE
  • Trūkstamų duomenų tvarkymas ( NULL)
  • Naudojant IS NULLirIS NOT NULL
  • Operatorių palyginimas su datomis ir laikais
  • Egzistavimas naudojant EXISTS/NOT EXISTS
  • Operatoriai bitais
  • Išvada

Duomenų bazės nustatymas

Norėdami filtruoti savo duomenis, pirmiausia, žinoma, turime jų turėti.

Šiuose pavyzdžiuose naudosime „PostgreSQL“, tačiau čia pateiktos užklausos ir sąvokos bus lengvai išverstos į bet kurią kitą modernią duomenų bazių sistemą (pvz., „MySQL“, „SQL Server“ ir kt.).

Norėdami dirbti su savo „PostgreSQL“ duomenų baze, galime naudoti psql- interaktyvią „PostgreSQL“ komandų eilutės programą. Jei turite kitą duomenų bazės klientą, su kuriuo jums patinka dirbti, tai taip pat puiku!

Norėdami pradėti, sukurkime savo duomenų bazę. Kai „PostgreSQL“ jau įdiegta, galime paleisti psqlkomandą createdb savo terminale, kad sukurtume naują duomenų bazę. Aš paskambinau savo fcc:

$ createdb fcc 

Tada pradėkime interaktyvią konsolę naudodami komandą psqlir prisijunkite prie duomenų bazės, kurią ką tik sukūrėme naudodami \c :

$ psql psql (11.5) Type "help" for help. john=# \c fcc You are now connected to database "fcc" as user "john". fcc=# 

Vartotojų kūrimas

Dabar, kai turime duomenų bazę, sukursime duomenų bazės lentelę, kad galėtume modeliuoti potencialų vartotoją savo išgalvotoje sistemoje.

Mes pavadinsime šią lentelę usersir kiekviena šios lentelės eilutė atstovaus vieną iš mūsų vartotojų.

Šioje userslentelėje bus stulpeliai, kuriuos tikėtume apibūdinti vartotojui, pvz., Vardas, el. Pašto adresas ir amžius.

Mūsų psqlsesijos metu sukurkime userslentelę:

CREATE TABLE users( id SERIAL PRIMARY KEY, first_name TEXT NOT NULL, last_name TEXT NOT NULL, email TEXT NOT NULL, age INTEGER NOT NULL );

Išvestis rodo, CREATE TABLEkurios lentelės sukūrimo priemonės buvo sėkmingos.

Pastaba: Aš išvaliau psqlšių pavyzdžių išvestį, kad būtų lengviau ją perskaityti, todėl nesijaudinkite, jei čia rodoma išvestis nėra tokia, kokią matėte savo terminale.

Pažvelkime į mūsų vartotojų lentelės turinį:

SELECT * FROM users; id | first_name | last_name | email | age ----+------------+-----------+-------+----- (0 rows) 

Mes neįtraukėme jokių duomenų į savo lentelę, todėl tiesiog matome tuščią lentelės struktūrą.

Jei nesate susipažinę su SQL užklausomis, ta, kurią ką tik vykdėme SELECT * FROM users, yra viena iš paprasčiausių, kurias galite parašyti.

Raktinis žodis SELECTnurodo, kurį stulpelį (-ius) norite grąžinti ( *reiškia „visi stulpeliai“), o FROMraktinis žodis nurodo, iš kurios lentelės norite pasirinkti (šiuo atveju users).

Taigi SELECT * FROM usersiš tikrųjų reiškia grąžinti visas lentelės eilutes ir stulpelius users.

Jei norėtume iš userslentelės grąžinti konkrečius stulpelius , galėtume juos pakeisti SELECT *, pvz SELECT id, name FROM users., Stulpeliais, kuriuos norime grąžinti .

Įterpiami vartotojai

Tuščia lentelė nėra labai įdomi, todėl įterpkime keletą duomenų į savo lentelę, kad galėtume praktikuoti užklausas pagal ją:

INSERT INTO users(first_name, last_name, email, age) VALUES ('John', 'Smith', '[email protected]', 25), ('Jane', 'Doe', '[email protected]', 28), ('Xavier', 'Wills', '[email protected]', 35), ('Bev', 'Scott', '[email protected]', 16), ('Bree', 'Jensen', '[email protected]', 42), ('John', 'Jacobs', '[email protected]', 56), ('Rick', 'Fuller', '[email protected]', 16);

Jei vykdome tą įterpimo sakinį savo psqlsesijoje, matome išvestį INSERT 0 7. Tai reiškia, kad į savo lentelę sėkmingai įterpėme 7 naujas eilutes.

Jei SELECT * FROM userspakartotinai vykdysime užklausą, dabar pamatysime šiuos duomenis:

SELECT * FROM users; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 2 | Rick | Fuller | [email protected] | 16 3 | Bree | Jensen | [email protected] | 42 4 | Bev | Scott | [email protected] | 16 5 | Xavier | Wills | [email protected] | 35 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (7 rows) 

Duomenų filtravimas naudojant WHERE

Iki šiol mes ką tik grąžinome visas eilutes iš savo stalo. Tai yra numatytasis užklausos elgesys. Norėdami grąžinti selektyvesnį eilučių rinkinį, turime filtruoti eilutes naudodami WHEREsąlygą.

Yra daugybė būdų, kaip filtruoti mūsų eilutes, naudojant WHEREsąlygą. Paprasčiausias operatorius mes galime naudoti yra lygybė operatorius: =.

Tarkime, kad norėjome rasti vartotojų, kurių vardas buvo „Jonas“:

SELECT * FROM users WHERE first_name = 'John'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 7 | John | Smith | [email protected] | 25 (2 rows) 

Čia mes pridėti raktažodį WHEREmūsų užklausos po to lygybės pareiškimas: first_name = 'John'.

Our database first looks at the FROM keyword to determine what data to fetch. So, the database will read this query, see FROM users, and go and fetch all of the rows for the users table from the disk.

Once all of the rows have been retrieved from the users table, it then runs the WHERE clause against each row and only returns rows where the first_name column value equals "John."

In our data, there are two rows that match that first name.

If we wanted to find a particular "John" in our system, we could query based on a column that we know is unique — like our id column.

To find the "John Jacobs" row specifically, we could query by his ID:

SELECT * FROM users WHERE id = 1; id | first_name | last_name | email | age ----+------------+-----------+------------------+----- 1 | John | Jacobs | [email protected] | 56 (1 row) 

Here only one record matched the condition of id = 1, so we only got back one row.

Logical operators (AND / OR / NOT)

We can filter by more than just the equality operator. We can also use the boolean logical operators that are found in most programming languages: and, or, and not.

In many programming languages and and or are represented by && and ||. In SQL, they're simply AND and OR.

Instead of querying by ID, let's try to find the record for the person named "John Smith." To do this, we can use an AND in our WHERE clause to look for both the first name and last name condition:

SELECT * FROM users WHERE first_name = 'John' AND last_name = 'Smith'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 7 | John | Smith | [email protected] | 25 (1 row) 

To find people with a first name of "John" or a last name of "Doe":

SELECT * FROM users WHERE first_name = 'John' OR last_name = 'Doe'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (3 rows) 

Čia mūsų rezultate buvo ir Johns , ir Jane Doe .

Tai ANDir ORsąlygos taip pat gali būti sujungtos grandine. Tarkime, kad norėjome rasti asmenį, pavadintą tiksliai „John Smith“, arba asmenį, kurio pavardė yra „Doe“:

SELECT * FROM users WHERE ( first_name = 'John' AND last_name = 'Smith' ) OR last_name = 'Doe'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (2 rows) 

Jei norėjome pavartykite šią sąlygą ir sužinoti vartotojams, kurie nėra pavadintas "John Smith", o taip pat jie negali turėti pavardę "Doe", mes galime pridėti NOToperatorių:

SELECT * FROM users WHERE NOT ( ( first_name = 'John' AND last_name = 'Smith' ) OR last_name = 'Doe' ); id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 4 | Bev | Scott | [email protected] | 16 5 | Bree | Jensen | [email protected] | 42 6 | John | Jacobs | [email protected] | 56 7 | Rick | Fuller | [email protected] | 16 3 | Xavier | Wills | [email protected] | 35 (5 rows)
Pastaba: kiekvienas turi savo asmeninį stilių, kaip jam patinka formuoti užklausas - darykite viską, kas jums prasminga!

Palyginimas operatoriai ( <, >, <=, >=)

Similar to other programming languages, SQL also the comparison operators: <, >, <=, >=.

Let's practice using these operators against our users' age column.

Let's say we wanted to find users that were eighteen years or older:

SELECT * FROM users WHERE age >= 18; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 3 | Bree | Jensen | [email protected] | 42 5 | Xavier | Wills | [email protected] | 35 6 | Jane | Doe | [email protected] | 28 7 | John | Smith | [email protected] | 25 (5 rows) 

What about users that are older than 25, but less than or equal to 35 years old?

SELECT * FROM users WHERE age > 25 AND age <= 35; id | first_name | last_name | email | age ----+------------+-----------+-------------------+----- 5 | Xavier | Wills | [email protected] | 35 6 | Jane | Doe | [email protected] | 28 (2 rows) 

Arithmetic operators (+, -, *, /, %)

We can also perform mathematical calculations on our data.

Our users table has an age column, what if we wanted to find half of each person's age?

SELECT *, age / 2 AS half_of_their_age FROM users; id | first_name | last_name | email | age | half_of_their_age ----+------------+-----------+---------------------+-----+------------------- 1 | John | Jacobs | [email protected] | 56 | 28 2 | Rick | Fuller | [email protected] | 16 | 8 3 | Bree | Jensen | [email protected] | 42 | 21 4 | Bev | Scott | [email protected] | 16 | 8 5 | Xavier | Wills | [email protected] | 35 | 17 6 | Jane | Doe | [email protected] | 28 | 14 7 | John | Smith | [email protected] | 25 | 12 (7 rows) 

Here we select all of the table columns (using SELECT *), and we also select a new aggregate calculation: age / 2. We also give this value a descriptive name (half_of_their_age) with an alias using the AS keyword.

We can also find who's age is an even number by using the modulus or remainder operator (%):

SELECT * FROM users WHERE (age % 2) = 0; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Jacobs | [email protected] | 56 2 | Rick | Fuller | [email protected] | 16 3 | Bree | Jensen | [email protected] | 42 4 | Bev | Scott | [email protected] | 16 6 | Jane | Doe | [email protected] | 28 (5 rows) 

We can find who's age is an odd number by changing our = condition to a "not equals" using != or :

SELECT * FROM users WHERE (age % 2)  0; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 5 | Xavier | Wills | [email protected] | 35 7 | John | Smith | [email protected] | 25 (2 rows) 

Existence operators (IN / NOT IN)

If we wanted to check that a column value existed in a list of values, we can use IN or NOT IN:

SELECT * FROM users WHERE first_name IN ('John', 'Jane', 'Rick'); id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 2 | Jane | Doe | [email protected] | 28 6 | John | Jacobs | [email protected] | 56 7 | Rick | Fuller | [email protected] | 16 (4 rows) 

Similarly, we can use NOT IN to negate that condition:

SELECT * FROM users WHERE first_name NOT IN ('John', 'Jane', 'Rick'); id | first_name | last_name | email | age ----+------------+-----------+------------------+----- 3 | Xavier | Wills | [email protected] | 35 4 | Bev | Scott | [email protected] | 16 5 | Bree | Jensen | [email protected] | 42 (3 rows) 

Partial matching using LIKE

Sometimes, we may want to search for rows based on a partial-search.

Say for example we wanted to find all users that signed up for our application using a Gmail address. We can do a partial match against a column using the LIKE keyword. We can also specify a wildcard (or "match anything") in the match string using %.

To find users with an email that ends in gmail.com:

SELECT * FROM users WHERE email LIKE '%gmail.com'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 (1 row) 

The string %gmail.com means "match anything that ends in gmail.com."

If we look back at our users data, we'll notice that we actually have two users with a gmail.com address:

('John', 'Smith', '[email protected]', 25), ('Jane', 'Doe', '[email protected]', 28), 

However, Jane's email has a capital "G' in her email address. Or previous query didn't pick up this record because it was matching exactly against gmail.com with a lowercase "g."

To do a case-insensitive match, we just need to substitute LIKE for ILIKE:

SELECT * FROM users WHERE email ILIKE '%gmail.com'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 2 | Jane | Doe | [email protected] | 28 (2 rows) 

The wildcard symbol % at the beginning of the string means anything that ends in "gmail.com" will be returned. That could be [email protected] or [email protected] — as long as it ends in gmail.com.

We can also add as many wildcards (%) as we want.

For example, the search term %j%o% will return any emails that follow the pattern followed by a j, followed by , followed by an o, followed by :

SELECT * FROM users WHERE email ILIKE '%j%o%'; id | first_name | last_name | email | age ----+------------+-----------+---------------------+----- 1 | John | Smith | [email protected] | 25 2 | Jane | Doe | [email protected] | 28 5 | Bree | Jensen | [email protected] | 42 6 | John | Jacobs | [email protected] | 56 (4 rows) 

Dealing with missing data (NULL)

Next let's look at how we deal with rows with columns that have missing data.

To do that, let's add another column to our users table: first_paid_at.

This new column will be a TIMESTAMP (similar to a datetime in other languages), and it will represent the first date and time that a user paid us money for our application. Maybe we want to send them a nice card or some flowers on the anniversary of using our app?

We could drop our users table using DROP TABLE users; and re-create it, but that would also delete all of the data in our table.

To change a table without dropping it and losing the data, we can use ALTER TABLE:

ALTER TABLE users ADD COLUMN first_paid_at TIMESTAMP; 

That command returns the result ALTER TABLE, so our ALTER query succeeded.

If we query our users table now, we'll notice that this new column doesn't have any data in it:

SELECT * FROM users; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 1 | John | Smith | [email protected] | 25 | 2 | Jane | Doe | [email protected] | 28 | 3 | Xavier | Wills | [email protected] | 35 | 4 | Bev | Scott | [email protected] | 16 | 5 | Bree | Jensen | [email protected] | 42 | 6 | John | Jacobs | [email protected] | 56 | 7 | Rick | Fuller | [email protected] | 16 | (7 rows) 

Our first_paid_at column is empty, and the result from our psql query shows it as an empty column. This column is not technically empty — it contains a special value that psql is choosing not to display in its output: NULL.

NULL is a special value in databases. It's the absence or lack of a value, and it doesn't behave as we expect it would.

To illustrate this, let's look at the simple SELECT statements below:

SELECT 1 = 1, 1 = 2; ?column? | ?column? ----------+---------- t | f (1 row) 

Here we simply selected 1 = 1 and 1 = 2. As we expect, the result of these two statements is t and f (or TRUE and FALSE). 1 is equal to 1, and 1 is not equal to 2.

Now let's try the same with NULL:

SELECT 1 = NULL; ?column? ---------- (1 row) 

We might expect this value to be FALSE, but the return value is actually NULL.

To visualize these NULLs a little better, let's set how psql displays NULL values using the \pset option:

fcc=# \pset null 'NULL' Null display is "NULL". 

Now if we run that query again we'll see the NULL output we expect:

SELECT 1 = NULL; ?column? ---------- NULL (1 row) 

So 1 is not equal to NULL, what about NULL = NULL?

SELECT NULL = NULL; ?column? ---------- NULL (1 row) 

Oddly enough, NULL is not equal to NULL.

It helps to think of NULL as an unknown value. Is an unknown value equal to 1? Well, we don't know — it's unknown. Is an unknown value equal to an unknown value? Again, it's unknown. In this way NULL makes a little more sense.

Using IS NULL and IS NOT NULL

We can't use the equality operator with NULL, but we can use two operators specifically designed for it: IS NULL and IS NOT NULL.

SELECT NULL IS NULL, NULL IS NOT NULL; ?column? | ?column? ----------+---------- t | f (1 row) 

These values come out as expect: NULL IS NULL is true, and NULL IS NOT NULL is false.

That's all fine and weird, but how do we use this?

Well first let's get some data in our first_paid_at column:

UPDATE users SET first_paid_at = NOW() WHERE id = 1; UPDATE 1 UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 month') WHERE id = 2; UPDATE 1 UPDATE users SET first_paid_at = (NOW() - INTERVAL '1 year') WHERE id = 3; UPDATE 1 

In those UPDATE statements above we've set three different users first_paid_at columns: User ID 1 to the current time (NOW()), User ID 2 to one month ago, and User ID 3 to one year ago.

First, let's find users that have paid us and users who haven't:

SELECT * FROM users WHERE first_paid_at IS NULL; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 4 | Bev | Scott | [email protected] | 16 | NULL 5 | Bree | Jensen | [email protected] | 42 | NULL 6 | John | Jacobs | [email protected] | 56 | NULL 7 | Rick | Fuller | [email protected] | 16 | NULL (4 rows) SELECT * FROM users WHERE first_paid_at IS NOT NULL; id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | [email protected] | 25 | 2020-08-11 20:49:17.230517 2 | Jane | Doe | [email protected] | 28 | 2020-07-11 20:49:17.233124 3 | Xavier | Wills | [email protected] | 35 | 2019-08-11 20:49:17.23488 (3 rows) 

Comparison operators with dates and times

Now that we have some data, let's use our same comparison operators against this new TIMESTAMP field.

Let's try to find users that paid us for the first within the past week. To do this, we can take the current time, NOW(), and subtract from it one week using the INTERVAL keyword:

SELECT * FROM users WHERE first_paid_at > (NOW() - INTERVAL '1 week'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | [email protected] | 25 | 2020-08-11 20:49:17.230517 (1 row) 

We could also use a different interval, such as three months ago:

SELECT * FROM users WHERE first_paid_at < (NOW() - INTERVAL '3 months'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+-----------------+-----+--------------------------- 3 | Xavier | Wills | [email protected] | 35 | 2019-08-11 20:49:17.23488 (1 row) 

Let's try to find users that first paid us between one to six months ago.

We could combine our conditions again using AND, but instead of using less than and greater than operators let's use the BETWEEN keyword:

SELECT * FROM users WHERE first_paid_at BETWEEN (NOW() - INTERVAL '6 month') AND (NOW() - INTERVAL '1 month'); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+-------------------+-----+---------------------------- 2 | Jane | Doe | [email protected] | 28 | 2020-07-11 20:49:17.233124 (1 row) 

Existence using EXISTS / NOT EXISTS

Another way to check for existence is to use EXISTS and NOT EXISTS.

These operators filter out rows by checking for the existence (or non-existence) of a condition. This condition is usually a query against another table.

To set this up, let's create a new table called posts. This table will hold posts that a user can make in our system.

CREATE TABLE posts( id SERIAL PRIMARY KEY, body TEXT NOT NULL, user_id INTEGER REFERENCES users NOT NULL ); 

It's a simple table. It only contains an ID, a field to store the post text (body), and a reference to the user that wrote the post (user_id).

Let's insert some data into this new table:

INSERT INTO posts(body, user_id) VALUES ('Here is post 1', 1), ('Here is post 2', 1), ('Here is post 3', 2), ('Here is post 4', 3); 

In the data that we inserted into the posts table, User ID 1 has two posts, User ID 2 has one post, and User ID 3 also has one post.

To find users that do have posts, we can use EXISTS.

The EXISTS keyword takes a subquery. If anything is returned from that subquery (even a row with just the value of NULL), the database will include that row in the result set.

From the PostgreSQL docs on EXISTS:

EXISTS argumentas yra savavališkas SELECT sakinys arba subquery. Subklausa vertinama siekiant nustatyti, ar ji pateikia eilučių. Jei jis grąžina bent vieną eilutę, „EXISTS“ rezultatas yra „teisingas“; jei paklausa negrąžina eilučių, EXISTS rezultatas yra „klaidingas“.

EXISTStiesiog ieško eilės iš potekstės egzistavimo - nesvarbu, kas joje yra.

Pateikiame naudotojų, turinčių įrašus, pavyzdį EXISTS:

SELECT * FROM users WHERE EXISTS ( SELECT 1 FROM posts WHERE posts.user_id = users.id ); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+---------------------------- 1 | John | Smith | [email protected] | 25 | 2020-08-11 20:49:17.230517 2 | Jane | Doe | [email protected] | 28 | 2020-07-11 20:49:17.233124 3 | Xavier | Wills | [email protected] | 35 | 2019-08-11 20:49:17.23488 (3 rows) 

Kaip ir tikėjomės, grįžome 1, 2 ir 3 vartotojai.

Mūsų EXISTSužklausa tikrina, ar postsįrašas user_idatitinka lentelės idstulpelį users. Grįžome 1, SELECTnes čia galime grąžinti bet ką - duomenų bazė tiesiog nori pamatyti, kad kažkas iš tikrųjų buvo grąžinta.

Similarly, we could find users that don't have any posts by changing EXISTS to NOT EXISTS:

SELECT * FROM users WHERE NOT EXISTS ( SELECT 1 FROM posts WHERE posts.user_id = users.id ); id | first_name | last_name | email | age | first_paid_at ----+------------+-----------+---------------------+-----+--------------- 4 | Bev | Scott | [email protected] | 16 | NULL 5 | Bree | Jensen | [email protected] | 42 | NULL 6 | John | Jacobs | [email protected] | 56 | NULL 7 | Rick | Fuller | [email protected] | 16 | NULL (4 rows) 

Finally, we could also re-write this query to use IN or NOT IN instead of EXISTS or NOT EXISTS, like this:

SELECT * FROM users WHERE users.id IN ( SELECT user_id FROM posts ); 

This technically works, but as a general rule if you are testing for existence of another record it is generally more performant to use EXISTS. The IN and NOT IN operator are generally better used for checking a value against a static list like we did earlier:

SELECT * FROM users WHERE first_name IN ('John', 'Jane', 'Rick'); 

Bitwise operators

Although in practice the bitwise operators are not often used, for completeness let's look at a simple example.

If we wanted to (for some reason) look at the age of our users in binary and play with flipping those bits around, we could use a variety of bitwise operators.

As an example, let's look at the bitwise "and" operator: &.

SELECT age::bit(8) & '11111111' FROM users; ?column? ---------- 00010000 00101010 00111000 00010000 00011001 00011100 00100011 (7 rows)

To perform a bitwise calculation we first have to convert our age column from an integer to binary — in this example we cast it into an eight-bit binary string using ::bit(8).

Next we can "and" the result of our age in binary format with another binary string, 11111111.  Since a binary AND only returns 1 if both bits are 1's, this all 1's string keeps the output interesting.

Almost every other bitwise operator uses the same format:

SELECT age::bit(8) | '11111111' FROM users; -- bitwise OR SELECT age::bit(8) # '11111111' FROM users; -- bitwise XOR SELECT age::bit(8) <> '00000001' FROM users; -- bitwise shift right

The bitwise "not" operator (~) is a little different in that it is applied to a single term — similar to the regular NOT operator:

SELECT ~age::bit(8) FROM users; ?column? ---------- 11101111 11010101 11000111 11101111 11100110 11100011 11011100 (7 rows)

And finally, the most useful of the bitwise operators: concatenation.

A common use of this operator is to combine strings of text together. For example if we wanted to build a calculated property of a "full name" for users, we could use concatenation:

SELECT first_name || ' ' || last_name AS name FROM users; name -------------- Bev Scott Bree Jensen John Jacobs Rick Fuller John Smith Jane Doe Xavier Wills (7 rows)

Here we concatenate (or "combine") the first_name, a space (' '), and the last_name property to build a name value.

Conclusion

So that's an overview of basically every query filtering operator you'll ever need to use!

Yra dar keli operatoriai, kurių čia neaprašėme, tačiau šie operatoriai arba nėra naudojami labai dažnai, arba naudojami lygiai taip pat, kaip ir aukščiau, todėl jie neturėtų kelti jums problemų.

Jei jums patiko šis įrašas, aš čia rašau panašius dalykus savo tinklaraštyje.

Ačiū, kad skaitėte!

Jonas