1 / 24

Chapter 12. T-SQL Essentials

Chapter 12. T-SQL Essentials. Syed Rizvi. 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. Joins: Querying Data from Multiple Tables.

osric
Download Presentation

Chapter 12. T-SQL Essentials

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  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

More Related