SQL Server/T-SQL Tutorial/Query/Some

ANY and SOME

5> CREATE TABLE stores(
6>    stor_id        char(4)           NOT NULL,
7>    stor_name      varchar(40)           NULL,
8>    stor_address   varchar(40)           NULL,
9>    city           varchar(20)           NULL,
10>    state          char(2)               NULL,
11>    zip            char(5)               NULL
12> )
13> GO
1> insert stores values("1","B","567 Ave.","Tustin",   "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89  St.", "Fremont",  "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE discounts(
4>    discounttype   varchar(40)       NOT NULL,
5>    stor_id        char(4) NULL              ,
6>    lowqty         smallint              NULL,
7>    highqty        smallint              NULL,
8>    discount       dec(4,2)          NOT NULL
9> )
10> GO
1>
2> insert discounts values("Initial Customer",  NULL,   NULL, NULL, 10.5)
3> insert discounts values("Volume Discount",   NULL,   100,  1000, 6.7)
4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3>
4>    SELECT stor_id AS "Store ID", stor_name AS "Store Name"
5>    FROM stores
6>    WHERE stor_id != SOME
7>       (SELECT stor_id FROM discounts WHERE stor_id IS NOT NULL)
8> GO
Store ID Store Name
-------- ----------------------------------------
1        B
2        N
3        T
4        F
(4 rows affected)
1>
2> drop table stores;
3> drop table discounts;
4> GO

less than SOME

2>
3> CREATE TABLE stores(
4>    stor_id        char(4)           NOT NULL,
5>    stor_name      varchar(40)           NULL,
6>    stor_address   varchar(40)           NULL,
7>    city           varchar(20)           NULL,
8>    state          char(2)               NULL,
9>    zip            char(5)               NULL
10> )
11> GO
1> insert stores values("1","B","567 Ave.","Tustin",   "CA","92789")
2> insert stores values("2","N","577 St.", "Los Gatos","CA","96745")
3> insert stores values("3","T","679 St.", "Portland", "OR","89076")
4> insert stores values("4","F","89  St.", "Fremont",  "CA","90019")
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> CREATE TABLE discounts(
4>    discounttype   varchar(40)       NOT NULL,
5>    stor_id        char(4) NULL              ,
6>    lowqty         smallint              NULL,
7>    highqty        smallint              NULL,
8>    discount       dec(4,2)          NOT NULL
9> )
10> GO
1>
2> insert discounts values("Initial Customer",  NULL,   NULL, NULL, 10.5)
3> insert discounts values("Volume Discount",   NULL,   100,  1000, 6.7)
4> insert discounts values("Customer Discount", "8042", NULL, NULL, 5.0)
5> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>    SELECT stor_id AS "Store ID", stor_name AS "Store Name"
2>    FROM stores
3>    WHERE stor_id < SOME
4>    (SELECT stor_id FROM discounts WHERE stor_id IS NOT NULL)
5> GO
Store ID Store Name
-------- ----------------------------------------
1        B
2        N
3        T
4        F
(4 rows affected)
1>
2> drop table stores;
3> drop table discounts;
4> GO
1>