SELECT C.ID D.NAME FROM dbo.test as c CROSS JOIN dbo.testt as d; //把多个表的列放在一起
SELECT E.empid, E.firstname,E.lastname,O.orderid FROM HR.Employess AS E JOIN Sale.orders AS O ON E.empid=O.empid; //两个相同的地方内联接查询
SELECT E.empid, E.firstname,E.lastname,O.orderid FROM HR.Employess AS E JOIN Sale.orders AS O ON E.empid=O.empid; AND E.ID=O.ID //多个相同的地方内联接查询
SELECT E.empid, E.firstname,E.lastname,O.orderid FROM HR.Employess AS E JOIN Sale.orders AS O ON E.empid<O.empid; //与内联接相同,而内联接是等值联接,包含等号以外的运算符的联接是不等联接
SELECT c.custid,c.companyname,o.orderid, OD.productid,OD.qty FROM Sales.customers AS c JOIN SALES.Orders as o ON c.custid=o.custid JOIN Sales.OrderDetails AS OD ON o.orderid=OD.orderid;
seclect c.custid,c.companyname from sales.customers as c left outer join sales.orders as o On c.custid=o.custid where o.orderis is null; //查询没有订单的客户 //左外联接 left outer join 保留左边表和相同部分 //右外联接 right outer join 保留右边表和相同部分 // full outer join 保留左右两边表和相同部分 //PS:外联接保留内部行和外部行,而内联接只返回内部行
select orderid,orderdate,empid from sales.orders where orderid=(select max(o.orderid) from sales.orders as o); //独立标量子查询,对于有效的标量子查询,返回值不能超过一个 //独立多值子查询,将=换成in 返回值可以多个 //独立的意思是子查询都可以单独拿出来用 不依赖外部查询
select custid,orderid,orderdate,empid from sales.orders as o1 where orderid= (select max(o2.orderid) from sales.orders as o2 where o2.custid=o1.custid); //子查询会为每个外部行单独计算一次 //类似两层For循环
DECLARE @NAME =值
Select id,name from stu UNION//并集 intersect//交集 except//差集 select id,nanme from teacher; //UNION 去掉重复行 //UNION ALL 保留