256 Par t I EXAM PREPARATION Using INNER
256 Par t I EXAM PREPARATION Using INNER JOIN The INNER JOIN statement is the easiest and most often used join statement. It s very simple; it relates one table to another table; and it returns a rowset where the two tables match up. For example: SELECT * FROM Person INNER JOIN Sales on Person.PersonID = Sales.PersonID What s that going to do? It returns all the rows where there is a person who has made a purchase. The columns returned are all the columns from both tables, with the columns from the Person table first. The resultset is wider than you probably want: PersonID FirstName LastName PersonID ProductID QtyPurchased DatePurchased 1 Danny Jones 1 37 4 2001-07-22 16:50 1 Danny Jones 1 38 3 2001-07-22 16:50 3 Scott Smith 3 39 1 2001-07-22 16:50 4 Alex Riley 4 51 1 2001-07-22 16:50 4 Alex Riley 4 47 1 2001-07-22 16:50 9 Shelly Alexander 9 37 10 2001-07-22 16:50 9 Shelly Alexander 9 38 5 2001-07-22 16:50 That s probably not what you want in a resultset. You ll get a fairly useful resultset if you write the SELECT statement like this, specifying the column names, instead: SELECT FirstName, LastName, ProductID, QtyPurchased FROM Person INNER JOIN Sales ON Person.PersonID = Sales.PersonID This query shows the people in the database to whom you have sold things. It doesn t show all the people in the database, because not everyone has bought something. It also shows some people more than once, because they bought several things. Now look more closely at the statement after the keyword FROM. First, data is selected from the Person table, and followed by an INNER JOIN to the Sales table. The ON clause then tells SQL Server how the tables are related. That ON clause says to return rows where the PersonID column in the two tables is the same. Notice that the column name is specified as Person.PersonID. Both tables have a
If you looking for unlimited one inclusive web hosting plan please check web hosting plan website.