Summary of sub-query statements supported and not supported by Sybase

Abstr]

"Sybase database does not support subqueries, so ......", We often hear this in database development. Is Sybase really true? Let's go to the sybase database to see which subquery statements are supported and not supported respectively. Hope to help you.

【Key words]

Sybase 12.5.0.3. subquery

[References]

Microsoft SQL Server 2000 books online

[Test Environment]

Adaptive Server Enterprise/12.5.0.3/Ebf 11449 ESD #4/P/NT (ix86)/OS 4.0/rel12503/1939/32 -bit/opt/SAT Sep 20 22:28:57 2003

1. subqueries supported by Sybase
1. subqueries in update, delete, and insert statements
Subqueries can be nested in update, delete, insert, and select statements.

The following query doubles the price of all books published by New Moon books. This query updates the titles table. Its subqueries reference the publishers table.

Update titles

Set price = price * 2

Where pub_id in

(Select pub_id

From publishers

Where pub_name = 'new moon books ')

The following nested query can be used to delete the records of all publishers who published commercial books:

Delete publishers

Where pub_id in

(Select pub_id

From titles

Where type = 'business ')

2. subqueries using exists, not exists, in, and not in
When you use the exists keyword to introduce a subquery, it is equivalent to performing an existence test. The where clause of the external query tests whether the row returned by the subquery exists. The subquery does not actually generate any data; it returns only true or false values.

The subquery syntax introduced by exists is as follows:

Where [not] exists (subquery)

The following query uses exists as an example to find the names of all publishers who have published commercial books:

Select pub_name

From publishers

Where exists

(Select *

From titles

Where pub_id = publishers. pub_id

And type = 'business ')

The following query finds the names of all publishers who have not published commercial books:

Select pub_name

From publishers

Where pub_id not in

(Select pub_id

From titles

Where pub_id = publishers. pub_id

And type = 'business ')

3. subqueries using comparison Operators
A subquery can be composed of a comparison operator (=, <>,>, >=, <,!> ,! <Or <=.

To use a subquery introduced by a comparison operator, you must be familiar with the nature of data and problems to understand whether the subquery returns a value or a value list.

For example, the following query shows that the price is not the lowest.

Select distinct title

From titles

Where price>

(Select Min (price)

From titles)

4. subqueries using aliases
Related subqueries can be used to select data from tables referenced by external queries. In this case, you must use the table alias (also known as the relevant name) to specify which table to use for reference. For example, you can use a subquery to find the types of books published by multiple publishers. Aliases must be used to distinguish two different roles in the titles table.

Select distinct t1.type

From titles T1

Where t1.type in

(Select t2.type

From titles T2

Where t1.pub _ id <> t2.pub _ id)

5. Related subqueries in the having clause
Related subqueries can also be used in the having clause of external queries. The following query can find the book type whose maximum prepayment is twice the average prepayment of the given type.

Select t1.type

From titles T1

Group by t1.type

Having max (t1.advance)> = all

(Select 2 * AVG (t2.advance)

From titles T2

Where t1.type = t2.type)

6. Test script:
-- Test creation statement and test data:
If exists (select * From sysobjects where name = 'tidles ')
Drop table titles
Go
Create Table titles (
Title_id int not null,
Title varchar (80) not null,
Type char (12) default 'uncided' not null,
Pub_id char (4) null,
Price money null,
Advance money null,
Royalty int null,
Ytd_sales int null,
Notes varchar (200) null,
Pubdate datetime default getdate () not null
)
Go
Alter table titles add constraint pk_titles primary key (title_id)
Go
Insert into titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
Values (1, 'database system print', 'database', '123', 0001, 1,100, '', getdate ())
Go
Insert into titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
Values (2, 'informix uses quanshu ', 'database', '2013', 0001, 160,140, '', getdate ())
Go
Insert into titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
Values (3, 'eclipse ', 'computer', '20160301', 0002, 1,100, '', getdate ())
Go
Insert into titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
Values (4, 'Bank management', 'business', '201312', 0002, 1,100, '', getdate ())
Go
Insert into titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
Values (5, 'Company management', 'business', '201312', 0001, 132,100, '', getdate ())
Go
Insert into titles (title_id, title, type, pub_id, price, advance, royalty, ytd_sales, notes, pubdate)
Values (6, 'oracle prinle le', 'database', '000000', 0002, 1,200, '', getdate ())
Go
If exists (select * From sysobjects where name = 'herhers ')
Drop table publishers
Go
Create Table Publishers (
Pub_id char (4) not null,
Pub_name varchar (40) null,
City varchar (20) null,
State char (2) null,
Country varchar (30) default 'usa' null
)
Go
Alter table publishers add constraint pk_publishers primary key (pub_id)
Go
Insert into Publishers (pub_id, pub_name, city, state, country)
Values ('20140901', 'machinery Publishing House ', 'beijing', '1', 'China ')
Insert into Publishers (pub_id, pub_name, city, state, country)
Values ('20140901', 'new moon books ', 'London', '1', 'England ')
Go

Ii. subqueries not supported by Sybase
1. Sybase does not support using subqueries in the from clause
You cannot use subqueries in the from clause in Sybase.

For example, select * from (select * from a) T

The solution is to use temporary tables and views.

2. Sybase does not support top paging query.
In Microsoft's SQL Server, the top syntax is introduced in its T-SQL, through this syntax can be very convenient to achieve paging query, SQL statement for (CTSI business as an example, idcdr is the Unique Identifier Field of the table ):
Select top records per page * From ctsiinforecord01

Where idcdr not in
(Select top pages * Number of records per page idcdr

From ctsiinforecord01

Order by idcdr)

Order by idcdr

In the actual query, you only need to modify the top record count of the subquery.

Unfortunately, this top syntax is not supported in Sybase. The corresponding syntax is set rowcount. However, this syntax cannot be placed in subquery statements. Therefore, the preceding method cannot be implemented.

Solution 1: create a temporary table and query by PAGE

Based on the implementation idea of this method, a temporary table is introduced and implemented with paging query. The SQL statement is as follows:

Set rowcount page number * Number of records per page

Select idcdr into # ctsitable from ctsiinforecord01 order by idcdr

Set rowcount number of records per page

Select * From ctsiinforecord01 where idcdr not in

(Select idcdr from # ctsitable) order by idcdr

Drop table # ctsitable

Note: # ctsitable is a temporary table in the temporary database tempdb;

In Sybase, order by cannot be introduced into subqueries;
If you query the first page, you can directly query it without creating a temporary table:

Set rowcount number of records per page

Select * From ctsiinforecord01 order by idcdr

Solution 2: query by Page Based on the idcdr Condition

Assume that the Unique Identifier Field of the table is idcdr. If order by is used for sorting (in ascending order by default), all records queried on the next page are displayed on the premise that the number of records per page is fixed and the query conditions are the same, the idcdr value must be greater than the idcdr value recorded at the end of the previous page. If we obtain the idcdr value of the last record after each query, and then introduce this condition in the next query, the result will inevitably be the next page result queried Based on the condition. The method is as follows:

Set rowcount number of records per page

Select * From ctsiinforecord where idcdr> the idcdr value of the last record on the previous page. Order by idcdr

If the query is a previous page, the opposite is true. You need to obtain the idcdr value of the first record on the next page:

Set rowcount number of records per page

Select * From ctsiinforecord where idcdr <idcdr value of the first record on the next page

Note: If you query the homepage, The idcdr value condition is deleted.

If the last page of the query is deleted, the sorting is changed to descending order when the idcdr value conditions are deleted.

Iii. Summary
In summary, Sybase databases support subqueries and related subqueries using exists, not exists, in, not in, having, and comparison operators. Sybase does not support subqueries in the from clause, top and order by clauses are not supported in subqueries. We don't have to make such a sigh that "sybase database does not support subqueries", and we will be more comfortable with understanding and using the characteristics of various databases.

 

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/liuzh501448/archive/2007/04/10/1559241.aspx