sql subquery tutorial

Gregory larsen,2014/03/05 the series This is part of the series: Stair Stairs T-sql: Beyond Basics Below is a more advanced aspect of the T-SQL DML that covers the Gregory Larsen from his staircase, T-SQL, or subqueries. in this staircase level 2 I discuss how to use subqueries in Transact-SQL statements. This stai

Tags: style blog http color ar sp Data div Arthttp://blog.csdn.net/c517984604/article/details/7052186 [ERR] 1242-subquery returns more than 1 row--Indicates that the subquery returned multiple rows of dataFor example:SELECT * FROM table1 where table1.colums= (select columns from table2)Workaround1,select * FROM table1 where column=any (select columns from table2)2,select * FROM table1 where column in (selec

Many SQL Server programmers are confused about the use of subqueries, especially nested subqueries (that is, subqueries contain a subquery ). Now let's trace this problem. There are two subquery types: standard and related. The standard subquery is executed once and the result is fed back to the parent query. Each row

subquery statementSome: and "any" mean the sameIn: Same meaning as "=any"Exists: Sub-query statement returns at least one record Example: Query for departments where all employees pay less than 2000 /* * Sub -query query all employees pay less than 2000 of the department */ @Test publicvoid childtest () { List). List (); for (Dept dept:list) { System.out.println (Dept.getdeptname ());

not exists (select * From t_to_order_insurance where between = t_to_order_tickets.order_id) and departure_time Summary: Observe step 1 of the original SQL Execution Plan| 10 | View | vw_nso_1 | 1120k | 13m | 26017 (7) | 00:05:13 |Vw_nso_1 indicates that Oracle has performed subquery decoding nesting (which is unlocked by default, This is where the problem is. If you don't want him to solve it, you can te

the comments below)--correct wordingSELECT * fromDbo. Table_a asaWHEREa.ID not inch(SELECTb.id fromDbo. Table_b asbWHEREb.ID is not NULL)--exclude null values to participate in operator comparisons --suggested modifications to the associated query method--correct wording 1SELECT * fromDbo. Table_a asaWHERE not EXISTS(SELECT * fromDbo. Table_b asbWHEREa.ID=b.id)--correct wording 2SELECT * fromDbo. Table_a asa Left OUTER JOINDbo. Table_b asB ona.ID

Label:/* Update--1 using an update with the associated subquery . Create a test table Create TABLE Table1 ( a varchar (ten), B varchar (ten), C varchar (ten), CONSTRAINT [pk_ Table1] PRIMARY KEY CLUSTERED ( a ASC ) ) on [PRIMARY] create TABLE table2 ( a varchar (ten), C varchar (ten), CONSTRAINT [pk_table2] PRIMARY KEY Clustered ( a ASC ) on [ Primary] go insert into Table1 values (' Zhao ', ' ASDs ',

SQL subquery, multi-Table query, and joint Query Subquery, multi-Table query, and joint QueryThese three concepts are interpreted slightly differently in different SQL versions, roughly as follows: JoinYou can check related SQL documents or buy a

subqueries, multiple table queries, federated queriesThese three concepts are interpreted slightly differently in different versions of SQL, roughly as follows: Join connectionCan look at the relevant SQL information, or buy a book of SQL 1, such as: SELECT * from TAB1 where ID in (SELECT id form tab2 where ...)The query sentence in parentheses above belongs to

of the subquery results. Also note that the all and any operators cannot be used alone, and can only be used in conjunction with single-line comparators (=, >, Cases: 1). Multi-row subqueries use the in operation symbol Example: The query took the name of the student whose teacher was named Rona (assuming only) Sql> Select Stname From Student where Stid in (SelectDistinct Stid from score where teid= (sele

(T.taskid,'__')= IsNull(N.taskid,'__')Group byT.taskidThe execution plan is as follows: (this looks much simpler than the above, imagine that performance will improve)When the amount of data is large, the performance aspect of using the left join will be greatly improved, note that when on, the null value is converted using IsNull.The two methods can be very different when the data volume performance is not very large, but when the data is large and the sub-query nesting complex may need to car

Here I see a post example. Requirement: use SQL statements to find out who will play basketball and badminton and find out the name-Hoby combination. Create table test (NAME varchar (20) not null, holobby varchar (20) not null );Insert into test values ('Adam ', 'bucketball ');Insert into test values ('bill ', 'bucketball ');Insert into test values ('bill ', 'football ');Insert into test values ('cyper', 'bucketball ');Insert into test values ('cype

(select Top 5 code from Renyuan)Select top 5*from Renyuan where code not in (select Top ten code from Renyuan) --a total of a few pagesSelect CEILING (COUNT (*)/5.0) from Renyuan --Find out all information about people older than 35 years in the sales department SELECT * from Renyuan where code in(select code from Renyuan where age>35 and ygbm=(select Bmcode from bumen where bmname= ' sales Department ')) --View all personnel information and replace the department number with the department nam

Tags: io ar sp on C R SQL BS as1. Check all subdirectories by root directoryWith Locs (Menu_id,menu_name_ch,parent_menu_id,loclevel)As(SELECT a.menu_id,a.menu_name_ch,a.parent_menu_id,0 as Loclevel from Sys_menu_info Awhere a.menu_id=75UNION AllSELECT a.menu_id,a.menu_name_ch,a.parent_menu_id,loclevel+1 from Sys_menu_info AINNER JOIN locs p on a.parent_menu_id=p.menu_id)SELECT * from Locs2. Start querying by sub-directoryWith Locs (Menu_id,menu_name_c

shows that the syntax of the subquery statement is not checked, but filtering plays a role, which is indeed a strange thing. If I mistakenly typed select xid in select * from tb1 where id in (select xid from tb2) into select id when I typed an SQL statement, then there will be a problem with the Retrieved Data (full extraction), but there is no prompt, because the syntax check is skipped. Therefore, this i