15个初学者必看的基础SQL查询语句

--Now we have used the simple join with out any condition this will display all the
-- records with duplicate data to avaoid this we see our next example with condition
SELECT * FROM Ordermasters,OrderDetails
-- Simple Join with Condition now here we can see the duplicate records now has been avoided by using the where checing with both table primaryKey field
SELECT * 
 FROM
 Ordermasters as M, OrderDetails as D
 where M.Order_NO=D.Order_NO
 and M.Order_NO='Ord_001'

-- Now to make more better understanding we need to select the need fields from both 
--table insted of displaying all column.
SELECT M.order_NO,M.Table_ID,D.Order_detail_no,Item_code,Notes,Qty
  FROM 
  Ordermasters as M, OrderDetails as D 
  where M.Order_NO=D.Order_NO         
 -- Now lets Join 3 table
 SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,
        I.Price*D.Qty as TotalPrice
  FROM 
  Ordermasters as M, OrderDetails as D,ItemMasters as I 
  where 
  M.Order_NO=D.Order_NO AND D.Item_Code=I.Item_Code

Inner Join,Left Outer Join,Right Outer Join and Full outer Join

下面是各种类型的连接查询代码：

--INNER JOIN 
--This will display the records which in both table Satisfy here i have used Like in where class which display the 
SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice
 FROM 
 Ordermasters as M Inner JOIN OrderDetails as D 
 ON M.Order_NO=D.Order_NO
 INNER JOIN ItemMasters as I 
 ON  D.Item_Code=I.Item_Code
 WHERE
 M.Table_ID like 'T%'
--LEFT OUTER JOIN 
--This will display the records which Left side table Satisfy
 SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice
 FROM 
 Ordermasters as M LEFT OUTER JOIN OrderDetails as D 
 ON M.Order_NO=D.Order_NO
 LEFT OUTER JOIN ItemMasters as I 
 ON  D.Item_Code=I.Item_Code
 WHERE
 M.Table_ID like 'T%'
--RIGHT OUTER JOIN 
--This will display the records which Left side table Satisfy
 SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice
 FROM 
 Ordermasters as M RIGHT OUTER JOIN OrderDetails as D 
 ON M.Order_NO=D.Order_NO
 RIGHT OUTER JOIN ItemMasters as I 
 ON  D.Item_Code=I.Item_Code
 WHERE
 M.Table_ID like 'T%'

--FULL OUTER JOIN 
--This will display the records which Left side table Satisfy
 SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice
 FROM 
 Ordermasters as M FULL OUTER JOIN OrderDetails as D 
 ON M.Order_NO=D.Order_NO
 FULL OUTER JOIN ItemMasters as I 
 ON  D.Item_Code=I.Item_Code
 WHERE
 M.Table_ID like 'T%'

10、Union合并查询

Union查询可以把多张表的数据合并起来，Union只会把唯一的数据查询出来，而Union ALL则会把重复的数据也查询出来。

Select column1,Colum2 from Table1
Union
Select Column1,Column2 from Table2

Select column1,Colum2 from Table1
Union All
Select Column1,Column2 from Table2

具体的例子如下：

--Select with different where condition which display the result as 2 Table result
select Item_Code,Item_Name,Price,Description FROM ItemMasters where price lt;=44
select Item_Code,Item_Name,Price,Description FROM ItemMasters where price gt;44

-- Union with same table but with different where condition now which result as one table which combine both the result.
select Item_Code,Item_Name,Price,Description FROM ItemMasters where price lt;=44
UNION
select Item_Code,Item_Name,Price,Description FROM ItemMasters where price gt;44

-- Union ALL with Join sample
 SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice
 FROM 
 Ordermasters as M (NOLOCK)  Inner JOIN OrderDetails as D 
 ON M.Order_NO=D.Order_NO INNER JOIN ItemMasters as I 
 ON  D.Item_Code=I.Item_Code WHERE I.Price lt;=44 
Union ALL
 SELECT M.order_NO,M.Table_ID,D.Order_detail_no,I.Item_Name,D.Notes,D.Qty,I.Price,I.Price*D.Qty as TotalPrice
 FROM 
 Ordermasters as M (NOLOCK)  Inner JOIN OrderDetails as D
 ON M.Order_NO=D.Order_NO INNER JOIN ItemMasters as I
 ON  D.Item_Code=I.Item_Code WHERE I.Pricegt;44

11、公用表表达式（CTE）——With语句

CTE可以看作是一个临时的结果集，可以在接下来的一个SELECT,INSERT,UPDATE,DELETE,MERGE语句中被多次引用。使用公用表达式可以让语句更加清晰简练。

（编辑：武汉站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!