Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 259
Chapter 5 ADVANCED DATA RETRIEVAL AND MODIFICATION 259 One more variation on the theme of inner joins. Imagine that you need to join a table back onto itself. For example, you want to find all the people with the same last name in the Person table. You d write a query like this one: SELECT P1.FirstName, P2.FirstName, P1.LastName FROM Person P1 INNER JOIN Person P2 ON P1.LastName = P2.LastName AND P1.FirstName < P2.FirstName In this example, the same table is used twice, joined to itself. The example produces the following output: FirstName FirstName LastName Danny Melissa Jones Chris Jennifer Avery Bryan Robin Decker The join also has an extra line in it. The P1.FirstName < P2.FirstName part prevents SQL Server from producing duplicate rows. Without that line in the JOIN clause, or at least in the WHERE clause, you d have each couple showing up twice, once with each name first. Now that you ve seen how INNER JOIN works, it s time to move on to the outer joins. Outer Joins You can use an outer join when you want to return the entire list of rows from one side of the join, and the matching rows from the other side of the join. There are three types of outer joins: LEFT, RIGHT, and FULL. A RIGHT OUTER JOIN, often abbreviated RIGHT JOIN, returns all the rows belonging to the table on the right side of the join, and where there is a matching row on the table on the left side, it returns the matching data. Conversely, LEFT OUTER JOIN returns all the rows from the table on the left side, and where there is a matching row on the table on the right side, it returns the matching data. A FULL OUTER JOIN returns all the rows from both sides with correlations where they exist. Most people read that for the first time and their eyes cross. Here are some examples to help you get back on track. Remember the query you saw earlier that returned all the records from Person with
If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.