数据库 夺标查询2

--等值连接
--查询所有产品及它们的供应商名称。
select ProductName,CompanyName from
dbo.Products inner join dbo.Suppliers
on dbo.Products.SupplierID=Suppliers.SupplierID

--非等值连接
--查询所有价格高于20的商品名及供应商
select ProductName,CompanyName,UnitPrice
from Products inner join dbo.Suppliers
on Products.SupplierID = Suppliers.SupplierID
where UnitPrice > 20
order by UnitPrice

select ProductName,CompanyName,UnitPrice
from Products inner join dbo.Suppliers
on Products.SupplierID = Suppliers.SupplierID
and UnitPrice > 20
order by UnitPrice

--=====================================
select *  from Products
--复制表
select * into  Products_new from Products
select ProductID,ProductName
into  Products_new2
from Products
where UnitPrice > 20
--联合查询
select  ProductID,ProductName  from Products
union all
select  ProductID,ProductName  from Products_new

-- SELECT select_list
-- [ INTO new_table ]
-- FROM table_source
-- [ WHERE search_condition ]
-- [ GROUP BY group_by_expression ]
-- [ HAVING search_condition ]
-- [ ORDER BY order_expression [ ASC | DESC ] ]

-------------------------------------------------------------------------------------------------

---------------------------------------------------------------------------------------------------

if exists(select * from sysobjects
    where name = 'a' and type = 'U')
drop table a
go
create table a
(
 id int,
 name varchar(6)
)
insert a values(1,'a')
insert a values(2,'b')
insert a values(3,'c')
insert a values(4,'d')

if exists(select * from sysobjects
    where name = 'b' and type = 'U')
drop table b
go
select * into b from a

select * from a
select * from b

--1.
select * from a left join b on a.id=b.id where a.id=1
--2.
select * from a left join b on a.id=b.id and a.id=1
--3.
select * from a left join b on a.id=b.id and b.id=1
--4.
select * from a left join b on a.id=1