need to understand the post: have knowledge of SQL, although not necessarily in SQL Server (another thing that will be useful if you use it).
When one is accustomed to the behavior of a database engine specific, start with a different one can be a problem, since it is common to find individual cases where the behavior of both differ, especially when manage nulls.
In my case, I was accustomed to working with the Informix database IBM company, the most powerful and fast that I have used no doubt, when I started using / experiencing the Express version of SQL Server 2005 . I had to migrate an application from one language (4GL Dynamic ) to the new version improved sick ( Genero 4GL ) and a database to another.
All went well until I had to make a SQL with a "jar" OUTER JOIN.
The program in question, we needed to relate the products table with the table of stock, but we wanted to leave all the items that belong, for example, the family 'AJUSA', regardless of whether there was a corresponding record in the table of stock. In addition, for this example, we only see the year 2008.
The query to execute in Informix is \u200b\u200bvery simple: SELECT
article .* FROM existence .*
article, OUTER existence
WHERE articulo.fam_art = existencia.fam_art
AND article . cod_art = existencia.cod_art
AND articulo.fam_art = 'AJUSA'
AND existencia.ejercicio = 2008 ORDER BY
articulo.fam_art
To verify that the statement is correct, just do a SELECT COUNT of records in the table of items whose family is 'AJUSA' and compare the result of that consultation. Both statements return the same number of records. And if we have a few items we can make a comparison, I assure you that never fails.
However, if we do the equivalent query in SQL Server, it would be: SELECT
article .* FROM existence .*
existence RIGHT OUTER JOIN item ON
existencia.fam_art = article.
fam_art AND existencia.cod_art = Articulo.cod_art
WHERE articulo.fam_art = 'AJUSA' AND
existencia.ejercicio = 2008 ORDER BY articulo.fam_art
We will have a nasty surprise, as only return records that exist in both tables specified criteria, or what is, DOES A FUCKING INNER JOIN.
Digging around I found this page which was confirmed this statement and how to solve the problem. The way to work in SQL Server is a bit Especialito say the least: first filtering is done WHERE clause and AFTER! the OUTER JOIN.
The solution proposed in sqlteam website is to include the selection criteria in clause of ON OUTER JOIN . So that we understand, if we remove all items related to the stock table that are of 2008 (whether or not in this table), the necessary consultation would be: SELECT
article .* FROM
existence .* existence RIGHT OUTER JOIN item ON
existencia.fam_art = articulo.fam_art
AND existencia.cod_art = articulo.cod_art
AND existencia.ejercicio = 2008
But our case we have an additional problem, and we need only items whose family is 'AJUSA'. The field is part of fam_art OUTER JOIN , so that this condition be ignored by the search engine database, so that the query: SELECT
article .* FROM existence .*
existence RIGHT OUTER JOIN
article ON existencia.fam_art = articulo.fam_art
AND existencia.cod_art = articulo.cod_art
AND articulo.fam_art = 'AJUSA'
AND existencia.ejercicio = 2008
... give the same result as the previous SQL statement; come on, that takes all items whether or not they stock in 2008.
But the solution is close to taking the order of execution of the clauses, because what we do is use the ON clause to indicate the filters on the table that specifies the OUTER (in our case, ' "existence") and then use the WHERE clause for the table you want to extract all records ("article" in this example): SELECT
article .* FROM existence .*
existence RIGHT OUTER JOIN ON article
existencia.fam_art = articulo.fam_art
AND existencia.cod_art = articulo.cod_art
AND existencia.ejercicio = 2008 WHERE
articulo.fam_art = 'AJUSA'
This query returns the records we wanted to achieve.
The OUTER JOIN is used very rarely and is difficult to find information on this happy problem so I decided that as precious information had to be on this blog, which gets many visits, so that knowledge quickly spread XD .
Internet, where women are men, men are women and the kids are FBI agents 
0 comments:
Post a Comment