QUESTION POSED ON: 17 October 2002
I have a rather simple question: What does "SELECT 1 FROM ..." accomplish? I am debugging a software program that is sending a "SELECT 1" statement to the database, and I am basically curious. Does it simply select one column from the record it finds?
EXPERT RESPONSE
Great question. No, it selects the same column from all the rows it finds!
I would be surprised to see a "SELECT 1" query all by itself as a stand-alone query. By itself, it's useless. Like all SELECT statements, it returns a result set, and in this case, the result set consists of a number of rows, where each row has exactly one column, and where the value of that column is an integer 1. In other words, there's a 1 in each row, and nothing else! The result set has as many rows as there are in the table being selected from, subject to WHERE conditions. Hence the result set, by itself, a table of 1s, is useless. The only meaningful information you can get from it is the number of rows, and that can be obtained a lot more efficiently by using COUNT(*).
More likely, you saw it in a subselect. SELECT 1 or SELECT * or SELECT NULL are constructions commonly used in an EXISTS subselect. In an EXISTS subselect, the database does not actually "retrieve" rows, and it does not always need to scan the entire result set for the subselect, because just one row will provide an answer. That answer is either TRUE or FALSE.
There's a somewhat contrived but illustrative example of an EXISTS subselect in one of my previous answers, How does WHERE EXISTS ( SELECT NULL... ) work? (22 February 2002).
分享到：
相关推荐
select * from company where comid in (select Max(comid) from company group by companyname) ``` 这条语句的作用是：首先，子查询`(select Max(comid) from company group by companyname)`会按照`companyname`...
if not exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) create table [Table1] ([col1] int,[col2] int) //添加字段 if not exists...
ORACLE INSERT INTO SELECT *FROM 分批提交例子
查询记录功能,从access数据库中读取,Adodc1.RecordSource = "select * from 订单表 where 订 单号='" + Text1(0).Text + "'" Adodc1.Refresh(VB6.0源代码编写)
WHERE sc.cno = '3-105' AND sc.degree > (SELECT degree FROM score WHERE sno = '109' AND cno = '3-105'); ``` ### 18. 显示score中选修多门课程的同学中分数为非最高分成绩的记录。 - **解析**：查询`score`...
帐目查询(VB6.0源代码编写)'按日期查询并汇总商品销售信息 Adodc1.RecordSource = "select * from xsd where 日期 between " + Chr(35) + Str(DTPicker1.Value) + Chr(35) + "and " + Chr(35) + Str(DTPicker2.Value...
AND COUNT(DISTINCT SC.C#) = (SELECT COUNT(*) FROM (SELECT DISTINCT C.C# FROM SC AS SC3 INNER JOIN Course AS C ON SC3.C# = C.C# WHERE SC3.S# = '1002') AS C) AND COUNT(DISTINCT SC.C#) = (SELECT COUNT...
SELECT TOP 1 @c1 = c1 FROM tb WHERE c1 NOT IN (SELECT c FROM @tb) AND c2 IN (SELECT c FROM @tb); INSERT INTO @tb SELECT @count, @c1 WHERE @c1 NOT IN (SELECT c FROM @tb); WHILE @@ROWCOUNT <> 0 ...
代码如下:select * from [Sheet1$] awhere ID not exists (select 1 from [Sheet1$] where PID=a.PID and ID>a.ID) select a.* from [Sheet1$] a inner join (select PID,max(ID) as max_id from [Sheet1$] group ...
execute immediate 'select * from tab1 where col1 = :1' using 'value'; ``` ##### 4.5 关于临时表使用 - 尽量避免使用临时表，以减少对系统资源的消耗。 - 示例： ```sql -- 错误示例 create global ...
例如：`hive> select 1 from dual where 1=1;` 2. **不等值比较**: 使用 `<>` 运算符进行不等值比较，如果两个表达式不相等，则返回TRUE，否则返回FALSE。例如：`hive> select 1 from dual where 1 <> 2;` 3. **...
eg: Select * from tableName Exec( select * from tableName ) Exec sp_executesql N select * from tableName -- 请注意字符串前一定要加N 2:字段名，表名，数据库名之类作为变量时，必须用动态SQL eg: ...
1. `SELECT * FROM student`：这个查询会返回student表中的所有列和所有行的信息。`*`是通配符，代表所有列。 2. `SELECT name FROM student`：这里我们仅选择name列，其余列将被忽略。 3. `SELECT name, age FROM...
1. FROM 语句：执行笛卡尔积（Cartesian Product），生成一个临时表。 2. WHERE 语句：执行选择（Selection），从临时表中选择满足条件的记录。 3. SELECT 语句：执行投影（Projection），从选择的记录中选择要检索...
- `SELECT 1 FROM systables WHERE tabid = 1; -- comment`，注释符`--`可用于单行注释。 3. **当前用户**： - `SELECT USER FROM systables WHERE tabid = 1;` 获取当前用户的用户名。 - `SELECT CURRENT_ROLE ...
FROM 表1 WHERE 表1.A = 表2.A) WHERE EXISTS (SELECT 1 FROM 表1 WHERE 表1.A = 表2.A); 尤其注意最后的外层where条件尤为重要，是锁定其批量更新数据的范围。 方法二： MERGE INTO 表2 USING 表1 ON (表2...
- 示例：使用 NOT EXISTS 替代：`SELECT * FROM T1 WHERE NOT EXISTS (SELECT 1 FROM T2 WHERE T1.ID = T2.ID)` #### 六、其他操作符注意事项 除了上述几点之外，还有一些特定的操作符使用时需要注意的地方： - *...
SELECT * FROM t1 WHERE NOT EXISTS (SELECT 1 FROM t2 WHERE t1.c2 = t2.c2); c1 c2 1 3 可以看到，NOT IN 语句出现了不期望的结果集，存在逻辑错误。如果我们查看这两个语句的执行计划，也会发现它们的执行计划...