Difference between SQL Server codes?

Question

For a school assignment, I have to create a database and run reports. I've created code and a classmate has also created code and it runs the same thing, but his is in a format I've not seen and don't quite understand. Here is mine: ``` SELECT Course.Name AS 'Course Name', Program.Name AS 'Program Name' FROM Course, Program, ProgramCourse WHERE ProgramCourse.CourseID = Course.ID AND ProgramCourse.ProgramID = Program.ID GO ``` And here's his: ``` CREATE VIEW NumberOfCoursePerProgram AS SELECT p.name AS ProgramName, c.name AS CourseName FROM Program p JOIN ProgramCourse pc ON pc.ProgramID = p.ID JOIN Course c ON c.ID = pc.CourseID GO ``` I ran both queries using the data in the tables I've created. They return practically the same results, just in a slightly different order but it fulfills the assignment question. Anyway, if I delete the `p` from `Program p` from his code, it returns an error The multi-part identifier "p.name" could not be bound. So how is SQL Server able to accept `p.n…

Accepted Answer

There's a few differences. First off, he's creating a `VIEW` rather than just a select statement:

```
CREATE VIEW NumberOfCoursePerProgram AS
```

Once the view is created, you can query the view just as you would a table:

```
SELECT * FROM NumberOfCoursePerProgram;
```

Second, he's using an ANSI JOIN rather than an implicit JOIN. His method is more modern and most likely considered more correct by today's standards:

```
JOIN ProgramCourse pc ON pc.ProgramID = p.ID
JOIN Course c ON c.ID= pc.CourseID
```

Rather than:

```
FROM Course, Program, ProgramCourse
```

Also, note he's assigning table aliases when he refers to a table:

```
FROM Program p
```

The `p` at the end allows you to substitute `p` rather than specify the entire table name of `Program` elsewhere in the query. For example, you can now say `WHERE p.Foo > 5` rather than `WHERE Program.Foo > 5`. In this case, it's just a shortcut and saves a few characters. However, suppose you were referring to the same table twice (for example, JOINing in two different rows on the same table). In that case, you might have to provide aliases for each table to disambiguate which one is which.

Difference between SQL Server codes?

Solution

Problem