WHERE l.value NOT IN ( SELECT value FROM t_right r ); 1 2 3 4 5 6 7 8 SELECT l.* FROM t_left l WHERE NOT EXISTS ( SELECT NULL FROM t_right r WHERE r.value = l.value ); 我们先把环境准备一下: postgres 11.9 1 2 3 4 5 6 7 8 9 10 CREATE TABLE t_left ( id INT NO...
先看看not in postgres=# SELECT l.id, l.valuepostgres-# FROM t_left lpostgres-# WHERE value NOT INpostgres-# (postgres(# SELECT valuepostgres(# FROM t_right rpostgres(# );id|value---+---20000|010000|060000|030000|040000|050000|070000|080000|090000|0100000|0(10rows)postgres=# explain ...
在我们平时写SQL时,如果遇到需要排除某些数据时,往往使用id <> xxx and id <> xxx,进而改进为id not in (xxx, xxx); 这样写没有问题,而且简化了SQL,但是往往有些极端情况,使用not in就会造成极大的性能损耗,例如: select * from test where id not in (select id from test_back) and info like '%t...
SELECTfirst_name,last_name,salaryFROMemployeesWHEREsalary=10000ORsalary=24000ANDlast_name='King';first_name|last_name|salary---+---+---Steven|King|24000.00Peter|Tucker|10000.00Janette|King|10000.00Harrison|Bloom|10000.00Hermann|Baer|10000.00(5rows) 由于AND优先级高,查询返回的是薪水为 24000 并且姓氏...
select e.empno,e.ename,e.job,e.sal from emp e(别名); 2.带条件查询 select 列名,列名,...,列名 from 表名 where --查询名字叫SMITH的员工的信息 select * from emp where ename='SMITH';(字符串类型加单引号) 3.in(后面跟集合或者子查询) not in ...
5.NOT IN操作符 与IN操作符相对应,NOT IN操作符用于查找不在给定值列表中的记录。例如,如果你想要查询年级不为 A、B 或 C 的学生,可以使用以下查询语句: SELECTfirst_name, last_nameFROMstudentsWHEREgradeNOTIN('A','B','C'); 这个查询将返回所有grade字段值不等于'A'、'B'或'C'的学生记录。NOT IN...
在我们变换了查询的逻辑,将staff_id 等于1的排除在外后,查询的效率里面排名 not in 为速度最快, not exists 排名第二 , any的速度与 not exists 类似。 select sum(pay.amount),sta.staff_id from staff as sta inner join ( select pay_z.amount,pay_z.staff_id ...
Exception in thread "main" org.postgresql.util.PSQLException: ERROR: column "continent" does not exist Hint: Perhaps you meant to reference the column "countries.Continent". Position: 8 已运行的查询如下: SELECT Continent FROM network.countries WHERE Continent IS NOT NULL AND Continent <> '' LIM...
NOT IN 以下SELECT 语句列出了AGE(年龄)字段不为 25 或 27 的数据: runoobdb=#SELECT*FROM COMPANY WHERE AGE NOT IN(25,27);id|name|age|address|salary---+---+---+---+---1|Paul|32|California|200003|Teddy|23|Norway|200006|Kim|22|South-Hall|450007|James|24|Houston|10000(4rows) BETWEEN...
sql SELECT ... WHERE b = 1 ORDER BY a; This allows the database engine to quickly find rows that use that predicate, as well as (often) retrieve a set of rows in sorted order without having to resort them. The database automatically updates the “cache” when a row changes in a ...