SQL - Outer Join | 1. Keydata. SQL > SQL JOIN >. Outer Join. Previously, we had looked at left join, or inner join, where we select rows common to the participating tables to a join. What about the cases where we are interested in selecting elements in a table regardless of whether they are present in the second table? We will now need to use the SQL OUTER JOIN command. The syntax for performing an outer join in SQL is database- dependent. For example, in Oracle, we will place an "(+)" in the WHERE clause on the other side of the table for which we want to include all the rows. Let's assume that we have the following two tables. Free C# Video Tutorials for beginners and intermediate programmers. Loading DocCommentXchange. Loading DocCommentXchange. Feature Oracle Postgres SQL Server IBM DB2 MySQL MariaDB Firebird H2 HSQLDB Derby SQLite; Queries: Window functions: Yes: Yes (*) DISTINCT is not supported inside a. ![]() Table Store_Information. Los Angeles. 15. 00. Jan- 0. 5- 1. 99. San Diego. 25. 0Jan- 0. Los Angeles. 30. 0Jan- 0. Boston. 70. 0Jan- 0. Table Geography. East. Boston. East. New York. West. Los Angeles. West. San Diegoand we want to find out the sales amount for all of the stores. If we do a regular join, we will not be able to get what we want because we will have missed "New York," since it does not appear in the Store_Information table. Therefore, we need to perform an outer join on the two tables above. SELECT A1. Store_Name, SUM(A2. Sales) SALESFROM Geography A1, Store_Information A2. WHERE A1. Store_Name = A2. Store_Name (+)GROUP BY A1. Store_Name; Note that in this case, we are using the Oracle syntax for outer join. Store_Name. SALESBoston. New York. Los Angeles. San Diego. 25. 0Note: NULL is returned when there is no match on the second table. In this case, "New York" does not appear in the table Store_Information, thus its corresponding "SALES" column is NULL. Next: SQL LEFT OUTER JOIN Copyright © 2. All Rights Reserved. Privacy Policy About Contact.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |