一、inner joins
A join based on the testing of equality between two tables is called equijoin. This kind of join is also called an inner join.
SELECT vend_name, prod_name, prod_price
FROM Vendors, Products
WHERE Vendors.vend_id = Products.vend_id;
等价于:
SELECT vend_name, prod_name, prod_price
FROM Vendors INNER JOIN Products
ON Vendors.vend_id = Products.vend_id;
Per the ANSI SQL specification, use of the INNER JOIN syntax is preferable.
二、self joins
SELECT cust_id, cust_name, cust_contact
FROM Customers
WHERE cust_name = (SELECT cust_name
FROM Customers
WHERE cust_contact = 'Jim Jones');
等价于:
SELECT c1.cust_id, c1.cust_name, c1.cust_contact
FROM Customers AS c1, Customers AS c2
WHERE c1.cust_name = c2.cust_name
AND c2.cust_contact = 'Jim Jones';
【No AS in Oracle Oracle users, remember to drop the AS.】
三、Natural Joins
A natural join is a join in which you select only columns that are unique. This is typically done using a wildcard (SELECT *) for one table and explicit subsets of the columns for all other tables. The following is an example:
SELECT C.*, O.order_num, O.order_date, OI.prod_id, OI.quantity, OI.item_price
FROM Customers AS C, Orders AS O, OrderItems AS OI
WHERE C.cust_id = O.cust_id
AND OI.order_num = O.order_num
AND prod_id = 'RGAN01';
The truth is, every inner join you have created thus far is actually a natural join, and you will probably never even need an inner join that is not a natural join.
四、Outer Joins
The join includes table rows that have no associated rows in the related table. This type of join is called an outer join. Such as:
1) List all products with order quantities, including products not ordered by anyone;
2)Calculate average sale sizes, taking into account customers who have not yet placed an order
When using OUTER JOIN syntax you must use the RIGHT or LEFT keywords to specify the table from which to include all rows (RIGHT for the one on the right of OUTER JOIN, and LEFT for the one on the left).
NOTE:It is important to note that the syntax used to create an outer join can vary slightly among different SQL implementations. The various forms of syntax described in the following section cover most implementations, but refer to your DBMS documentation to verify its syntax before proceeding.
1.LEFT OUTER JOIN
SELECT Customers.cust_id, Orders.order_num
FROM Customers LEFT OUTER JOIN Orders
ON Customers.cust_id = Orders.cust_id;
2.RIGHT OUTER JOIN
SELECT Customers.cust_id, Orders.order_num
FROM Customers RIGHT OUTER JOIN Orders
ON Orders.cust_id = Customers.cust_id;
3. FULL OUTER JOIN
SELECT Customers.cust_id, Orders.order_num
FROM Orders FULL OUTER JOIN Customers
ON Orders.cust_id = Customers.cust_id;
NOTE:FULL OUTER JOIN Support The FULL OUTER JOIN syntax is not supported by Access, MySQL, SQL Server, or Sybase.
分享到:
相关推荐
Teach Youself UML in 24 Hours
Sams Teach Youself SAP in 24 Hours(sap基础教程英文原版)
In fact,Teach Yourself C in 24 Hoursprovides a good starting point for you in C pro- gramming. It covers important topics in C programming,and lays a solid foundation for a serious beginner like you. ...
Teach.youself.UML.in.24.hours,3rd.edition.pdf 学uml最好的自学书
php初学者的首选图书,sams teach yourself 系列丛书之一。非常好
Teach.Youself.Perl.5.in.21.Days
国外经典Java教材,Sams Teach Youself Java in 21 Days第六版的源代码,本书涉及Java7和Android的知识,帮助读者编写高效的Java程序和Android应用! 作者:Rogers 译者:冯志祥等 出版社:人民邮电出版社
此资源尾pdf格式,英文教程,这个教程使用与初学者,学的较快。
java 源码书籍
这是SAMS Teach youself boorland C++Builder in 21 days一书的源代码,中文名Borland C++Builder3.0自学培训教程(希望公司出版).但此中文版并未配源码,使自学大打折扣.现在我把这套源码放在这,希望有这本书的读者能...
Here are FFTW lib and dll files compiled by GCC4.5 and VC2010 in Win7. But I suggest you compiling it by youself in you computer, or there will be some unpredictable errors!
10. Now you are ready to use the control in your Web form. Just drag the LoginControl icon in the ToolBox pane and drop it to the Web form. The control should appear in the form with all its child ...
SEO For 2016 – The Complete Do It Yourself SEO Guide
java 自学指南java初学者的宝典
4.row definition by youself (Render) 5.succinct code (size of origin code is under 10k, after compress it is under 3k), update easily. 6.easy study and use 7.easy custom css style Just a little ...
app lock for you to lock youself apps which you can not see for others
This is a library of the AnimatorSet,it has three move path and many animation,you can make a easy animation of view by youself. animation of View i did not finish it at this time Demo MovePath ...
Extract to any folder, and you can use it, make youself happy:) THIS IS A THIRD-PARTY SOFTWARE. IT IS PROVIDED "AS IS", WITHOUT ANY WARRANTY. If it does not meet your needs, please install the full ...
简明批处理教程22009年10月20日 星期二 下午 05:35 最近对于批处理技术的探讨比较热,也有不少好的批处理程序发布,但是如果没有一定的相关知识恐怕不容易看懂和理解这些批处理文件,也就更谈不上自己动手编写了,古...
char a[]=" lo v ely a dm inistration d youself my"; int p,q; int i; p=0,q=0; strPositon(a,&p,&q); printf("the most long word is :"); for(i=p;i;i++) printf("%c",a[i]); return 0; } void ...