Chapter 12. T-SQL Essentials

1. Chapter 12. T-SQL Essentials Syed Rizvi

2. Joins: Querying Data from Multiple Tables • Join: A link between two tables • A join will take place between at least one column in one table and a column from the joining table

3. Joins: Querying Data from Multiple Tables • Typically a join is done through primary key and foreign key but is not the requirement • The columns involved in the join do not have to be in any key within the tables involved in the join

4. Joins: Querying Data from Multiple Tables • One of the columns on one side of the join could be a concatenation of two or more columns as long as the end result is one column • Two columns that are being joined do not need to have the same name, as long as they both have similar data types.

5. Joins: Querying Data from Multiple Tables • It is not allowed to have one side of the JOIN a column name and on the other side a variable or literal that is really a filter that would be found in a WHERE statement.

6. Joins: General Syntax • FROM tablea [FULL[INNER|OUTER|CROSS]] JOIN tableb {ON tableb.column1 = tablea.column2 {AND|OR tableb.column...}}

7. INNER JOIN • An INNER JOIN joins the two tables, and where there is a join of data using the columns from each of the two tables • Notice that you don't need to define the word INNER. This is presumed if nothing else is specified

8. INNER JOIN Example SELECT s.ShareDesc,sp.Price,sp.PriceDate FROM ShareDetails.Shares s JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId

9. INNER JOIN Example SELECT s.ShareDesc,sp.Price,sp.PriceDate FROM ShareDetails.Shares s JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId AND sp.Price = s.CurrentPrice

10. OUTER JOIN • To return all the rows from one table where there is no join

11. LEFT OUTER JOIN • return all the rows in the left table, whether there is any data in the right table or not

12. LEFT OUTER JOINExample SELECT s.ShareDesc,sp.Price,sp.PriceDate FROM ShareDetails.Shares s LEFT OUTER JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId

13. LEFT OUTER JOINExample SELECT s.ShareDesc,sp.Price,sp.PriceDate FROM ShareDetails.Shares s LEFT OUTER JOIN ShareDetails.SharePrices sp ON sp.ShareId = s.ShareId WHERE sp.Price IS NULL

14. LEFT OUTER JOINExample

15. LEFT OUTER JOINExample

16. RIGHT OUTER JOIN • table on the right to return rows where there are no entries on the table in the left. • In our example, such a scenario does not exist, because it will break referential integrity; however, you can swap the tables around, which shows the same results as the first LEFT OUTER JOIN example

17. RIGHT OUTER JOINExample SELECT s.ShareDesc,sp.Price,sp.PriceDate FROM ShareDetails.SharePrices sp RIGHT OUTER JOIN ShareDetails.Shares s ON sp.ShareId = s.ShareId

18. RIGHT OUTER JOINExample

19. FULL OUTER JOIN LEFT OUTER JOIN and a RIGHT OUTER JOIN to be available at the same time This returns rows from both the left and right tables if there are no matching rows in the other table. So to clarify, if there is a row in the left table but no match in the right table, the row from the left table will be returned with NULL values in the columns from the right table, and vice versa. This time you are going to break referential integrity and insert a share price with no share.

20. FULL OUTER JOINExample INSERT INTO ShareDetails.SharePrices (ShareId, Price, PriceDate) VALUES (99999,12.34,'1 Aug 2008 10:10AM') SELECT s.ShareDesc,sp.Price,sp.PriceDate FROM ShareDetails.SharePrices sp FULL OUTER JOIN ShareDetails.Shares s ON sp.ShareId = s.ShareId

21. FULL OUTER JOINExample

22. CROSS JOIN • Every row in one table to be joined with every row in the joining table • if you had 10 rows in one table and 12 rows in the other table, you would see returned 120 rows of data (10 × 12) • Need to be used with extreme care

23. CROSS JOINExample SELECT s.ShareDesc,sp.Price,sp.PriceDate FROM ShareDetails.SharePrices sp CROSS JOIN ShareDetails.Shares s

24. CROSS JOINExample