![]() ![]() INNER JOIN Customers C ON O.CustomerID = C.CustomerID INNER JOIN OrderDetails OD ON O.OrderID = OD.OrderID SELECT O.OrderID, O.OrderDate, C.CustomerName, OD.ProductID, Here is a query that illustrates the use of inner and left outer joins. INSERT Orders(OrderID, OrderDate, CustomerID, DiscountCode)ĬREATE TABLE OrderDetails (OrderID int NOT NULL,ĬONSTRAINT pk_OrderDetails PRIMARY KEY (OrderID, ProductID),įOREIGN KEY (OrderID) REFERENCES Orders(OrderID) INSERT DiscountCodes(Code, Discount) VALUES ('ABCDEF', 5)ĬREATE TABLE Orders (OrderID int NOT NULL,ĬONSTRAINT pk_Orders PRIMARY KEY (OrderID),įOREIGN KEY (DiscountCode) REFERENCES DiscountCodes(Code),įOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID) VALUES(1, N'Garima Kulkarni'), (2, N'Priya Pol'), (3, N'Manisha Mishra')ĬREATE TABLE DiscountCodes (Code char(6) NOT NULL,ĬONSTRAINT pk_DiscountCodes PRIMARY KEY (Code) ![]() INSERT Customers (CustomerID, CustomerName) ![]() CREATE TABLE Customers(CustomerID int NOT NULL,ĬONSTRAINT pk_Customers PRIMARY KEY (CustomerID) Here are some tables for an order system, extremely simplified to only have the columns needed for the examples. Let’s now consider when we actually use these join types in practice and we will start with inner join and left outer join. I will not discuss right outer joins further in this article.įinally, with FULL OUTER JOIN, all rows on both sides are retained, leaving NULLs on the side where there is no match: aid adata bid bdata I personally find right outer joins very confusing, and I suspect that I am not alone. A right outer join is the same as a left outer join except that it is written the other way around. In the sample I mentioned above, there was only one single occurrence of a right outer join. If we change INNER JOIN to LEFT OUTER JOIN, both rows on the left side are retained, and we get NULL where a row is missing on the right side: aid adata bid bdataĪnd if we instead use RIGHT OUTER JOIN, both rows on the right side are retained, and we get NULL for the missing row on the left side: aid adata bid bdata We only get the rows with id = 2, as they are in both tables: aid adata bid bdata INSERT #b(bid, bdata) VALUES (2, N'Second'),(3, N'Third') INSERT #a(aid, adata) VALUES (1, N'First'), (2, N'Second')ĬREATE TABLE #b(bid int NOT NULL PRIMARY KEY, Here are two very simple tables: CREATE TABLE #a(aid int NOT NULL PRIMARY KEY, I will first cover some things about inner joins and left outer joins as this also helps to illustrates some of the issues you can run into with full outer joins.Īs a starter, let’s first repeat the different join types. I will also make some observations that are applicable to the other join types. This month we will look a little closer at this operator, because it’s one you easily can go wrong with, if you are not careful. In a collection of some 5000 stored procedures I had lying around, I found that were around 14,000 inner joins, 4,280 left outer joins, but only 18 full outer joins. You probably use both inner joins and left outer joins day in and day out, but the occasions when you need to use full outer joins are far less common. This article first appeared in the SQLServerGeeks Magazine. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |