Joins in Visual C#.NET

Painting QR Code 2d barcode in Visual C#.NET Joins

Joins are operations that allow you to match rows between tables. I informally call these operations horizontal because the virtual table resulting from a join operation between two tables contains all columns from both tables. I ll rst describe the different syntaxes for joins supported by the standard, and I ll also brie y mention legacy proprietary elements in T-SQL. I ll then describe the fundamental join types and their applications followed by further examples of joins. I ll also include a focused discussion on the internal processing of joins namely, join algorithms. You ll have a couple of chances to practice what you ve learned by trying to solve a problem that encompasses previously discussed aspects of joins.

T-SQL supports two different syntaxes for joins. A lot of confusion surrounds the two. When do you use each Which performs better Which is standard, and which is proprietary Will the older syntax be deprecated soon And so on. I hope this chapter will clear the fog. I ll start by saying that the ANSI standard supports two different syntaxes for joins, and neither syntax is in the process of deprecation yet. The join elements of the older standard are a complete part of the newer. This means that you can use either one without worrying that it will not be supported by Microsoft SQL Server sometime soon. SQL Server will not remove support for implemented features that were not deprecated by the standard. The older of the two syntaxes was introduced in ANSI SQL-89. What distinguishes it from the newer syntax is the use of commas to separate table names that appear in the FROM clause and the absence of the JOIN keyword and the ON clause:

The ANSI SQL-89 syntax had support only for cross and inner join types. It did not have support for outer joins. The newer syntax was introduced in ANSI SQL-92, and what distinguishes it from the older syntax is the removal of the commas and the introduction of the JOIN keyword and the ON clause:

ANSI SQL-92 introduced support for outer joins, and this drove the need for a separation of lters the ON lter and the WHERE lter. I ll explain this in detail in the outer joins section. Some people think that the comma-based syntax for joins in general is not standard, which is not true. Part of the confusion has to do with the fact that in the past, T-SQL supported a proprietary syntax for outer joins that was based on commas before SQL Server added support for the ANSI SQL-92 syntax. In particular, I m talking about the old-style proprietary outer join syntax, using *= and =* for left outer and right outer joins, respectively. In addition to not being standard, this syntax was problematic in the sense that in some cases the meaning of the query was ambiguous. SQL Server deprecated this syntax, and it is supported only under a backward-compatibility ag. In short, with cross and inner joins both the comma-based and JOIN keyword-based syntaxes are standard, while with outer joins only the JOIN keyword based syntax is standard. In the following section, I ll discuss both syntaxes and explain why I recommend that you stick to the ANSI SQL-92 join syntax even though the old-style syntax for cross and inner joins is standard.

As I describe the different fundamental join types cross, inner, and outer keep in mind the phases in logical query processing that I described in detail in 1, Logical Query Processing. In particular, keep in mind the logical phases involved in join processing. Each fundamental join type takes place only between two tables. Even if you have more than two tables in the FROM clause, the three logical query processing subphases of joins take place between two tables at a time. Each join results in a virtual table, which in turn is joined with the next table in the FROM clause. This process continues until all table operators in the FROM clause are processed. The fundamental join types differ in the logical subphases that they apply. Cross join applies only the rst (Cartesian product), inner join applies the rst and the second (Cartesian product and ON lter), and outer join applies all three (Cartesian product, ON lter, add outer rows).