EJEMPLOS IDENTITY INSERT Y CTE COMMON TABLES EXPRESION

-----------dejar sin efecto el primary key INSERT INTO SELECT AÑADIR DATOS A TABLA-------------------------------

SET IDENTITY_INSERT CONELEC..USERINFO ON

INSERT INTO CONELEC..USERINFO

select S.* from SirhaTime..USERINFO S

where S.BADGENUMBER NOT IN (select C.BADGENUMBER from CONELEC..USERINFO C) ORDER BY BADGENUMBER ASC

SET IDENTITY_INSERT CONELEC..USERINFO OFF

-----------TABLA TEMPORAL CTE COMMON TABLE EXPRESION -------------------------------

with allabound

(

select S.* from SirhaTime..USERINFO S where S.BADGENUMBER

NOT IN (select C.BADGENUMBER from CONELEC..USERINFO C) --ORDER BY BADGENUMBER ASC

)

INSERT INTO CONELEC..USERINFO
SELECT

[BADGENUMBER]

,[SSN]

,[NAME]

,[GENDER]

,[TITLE]

,[PAGER]

,[BIRTHDAY]

,[HIREDDAY]

,[STREET]

,[CITY]

,[STATE]

,[ZIP]

,[OPHONE]

,[FPHONE]

,[VERIFICATIONMETHOD]

,[DEFAULTDEPTID]

,[SECURITYFLAGS]

,[ATT]

,[INLATE]

,[OUTEARLY]

,[OVERTIME]

,[SEP]

,[HOLIDAY]

,[MINZU]

,[PASSWORD]

,[LUNCHDURATION]

,[MVerifyPass]

,[PHOTO]

,[Notes]

,[privilege]

,[InheritDeptSch]

,[InheritDeptSchClass]

,[AutoSchPlan]

,[MinAutoSchInterval]

,[RegisterOT]

,[InheritDeptRule]

,[EMPRIVILEGE]

,[CardNo]

,[OTAdmin]

,[OTPass]

,[OTPrivAdmin]

,[Pin1]

,[OpcionEmpleado]

,[SIRHAPASS]

,[EMAIL]

,[CHECKONLINE]

,[CHECKEMAIL]

,[Genero]

,[EstadoCivil]

,[LugarTrabajo]

,[Cargo]

,[TipoContrato]

,[FechaRenuncia]

,[Discapacidad]

,[CargasFamiliares]

,[TipoSangre]

,[TelCasa]

,[TelCel]

,[NombreContacto]

,[TelContacto]

,[Observacion]

,[Firma]

,[CHECKORDER]

,[Tvacacion]

,[MEN_USU]

,[POR_USU]

,[PRO_USU]

,[PRI_USU]

,[Svacacion]

,[Adicional]

FROM allabound

EJEMPLO CTE COMMON TABLE EXPRESIONS TABLAS TEMPORALES

EJEMPLO CTE COMMON TABLE EXPRESIONS

CTE: Common Table Expressions – SQL Server 2008

Rate This

WarNov

12 Dec 2010 7:01 PM

Este es el primero de una serie de artículos que estaré escribiendo acerca de características de SQL Server 2008 para desarrolladores.
Un CTE es un artefacto que nos mantiene en memoria el resultado de una consulta, para que lo podamos llamar luego dentro de esa misma consulta. Es como una “variable” temporal que nos evita tener que declarar una vista no persistente, o una tabla temporal.
La sintaxis es muy sencilla:
WITH <nombre de su CTE> <nombre de columnas>
AS
(
<query de origen>
)
SELECT * FROM <nombre de su CTE>
Por ejemplo:

Para qué es útil?
Imagínense por ejemplo que tienen una tabla de ventas donde están todas las ventas hechas y el ID de los vendedores que las hicieron.
Piensen que necesitan un reporte donde aparezca el total de ventas y el nombre del vendedor.
Los datos extendidos del vendedor están en otra tabla. Además como el reporte totaliza la cantidad de ventas se requiere un query con una función de agregado ( count(*) ) Dado que existe este agregado no es posible usar un simple join, sino que tendríamos que usar una subconsulta o una tabla temporal:

select Vendedores.FirstName, VentasAgrupadas.VentasTotales from
(
   SELECT Sales.SalesOrderHeader.SalesPersonID, COUNT(*) as VentasTotales
   FROM Sales.SalesOrderHeader
   WHERE Sales.SalesOrderHeader.SalesPersonID IS NOT NULL
   GROUP BY Sales.SalesOrderHeader.SalesPersonID
) as VentasAgrupadas
inner join Sales.vSalesPerson as Vendedores on Vendedores.SalesPersonID=VentasAgrupadas.SalesPersonID
order by VentasAgrupadas.VentasTotales

Como se aprecia esto es engorroso y poco claro.
Con un CTE es mucho más sencillo, ya que no existe la necesidad de la subconsulta sino que parece como si declaráramos una variable de tipo tabla o vista con los resultados que queremos:

WITH VentasAgrupadas(IdVendedor, VentasTotales)
as
(
  SELECT Sales.SalesOrderHeader.SalesPersonID, COUNT(*)
  FROM Sales.SalesOrderHeader
  WHERE Sales.SalesOrderHeader.SalesPersonID IS NOT NULL
  GROUP BY Sales.SalesOrderHeader.SalesPersonID
)

SELECT Vendedores.FirstName, VentasAgrupadas.VentasTotales
FROM Sales.vSalesPerson as Vendedores
INNER JOIN VentasAgrupadas ON Vendedores.SalesPersonID=VentasAgrupadas.IdVendedor
ORDER BY VentasAgrupadas.VentasTotales

Un caso aún más dramático puede observarse cuando queremos detectar los duplicados de una tabla.
Por ejemplo si tengo una tabla de productos con Ids distintos pero mismo nombre, la consulta luce así:

SELECT * FROM Products WHERE ProductID NOT IN
(SELECT MIN(ProductID) FROM Products as P
WHERE Products.ProductName=P.ProductName)

Exótico no?
Es lo que conocemos como Self-Join en SQL.
Eso me retorna todos los registros duplicados. Observen que se logra luego de extraer el mínimo id para los duplicados, selecciono todos los duplicados cuyo id no sea ese mínimo, lo que me da como resultado los registros redundantes:

Obviamente con un CTE la complejidad del query se reduce completamente:

WITH MinProductRecords AS
(
  SELECT MIN(ProductID) AS ProductID, ProductName
  FROM Products
  GROUP BY ProductName
  HAVING COUNT(*) > 1
)

SELECT Products
FROM Products AS P
INNER JOIN MinProductRecords AS MP
ON P.ProductName = MP.ProductName AND P.ProductID > MP.ProductID

Aquí aunque hay más código, es más claro y administrable, pues vemos cómo primero seleccionamos los mínimos Ids y luego hacemos un join donde los Ids sean mayores a estos mínimos. Así se obtienen los mismos resultados.
Entonces si quisiéramos además eliminar de inmediato los duplicados bastaría con reemplazar el último SELECT por un DELETE

SQL Server -- Inserting and Deleting Records Tutorial Part 3 of 5 -- I...

EXPLAIN EXTENDED

How to create fast database queries

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

with 17 comments

This series of articles is inspired by multiple questions asked by the site visitors and Stack Overflow users, including Tony, Philip, Rexem and others.

Which method is best to select values present in one table but missing in another one?
This:

view source print ?

1.SELECT l.*

2.FROM    t_left l

3.LEFT JOIN

4.t_right r

5.ON      r.value = l.value

6.WHERE   r.value IS NULL

, this:

view source print ?

1.SELECT l.*

2.FROM    t_left l

3.WHERE   l.value NOT IN

4.(

5.SELECT value

6.FROM    t_right r

7.)

or this:

view source print ?

1.SELECT l.*

2.FROM    t_left l

3.WHERE   NOT EXISTS

4.(

5.SELECT NULL

6.FROM    t_right r

7.WHERE   r.value = l.value

8.)

Differences between the methods

These methods are quite different.
First of all, LEFT JOIN / IS NULL and NOT EXISTS are semantically equivalent, while NOT IN is not. These method differ in how they handle NULL values in t_right
LEFT JOIN is guaranteed to return every row from t_left, and then filtering is applied to the values returned from t_right. If for some row in t_left there is no corresponding row in t_right (which means no row with that exact value is present in t_right), the row from t_left will be returned once, and the NULL values will be substituted instead of t_right's actual values.
Since NULL values can never satisfy an equality JOIN condition, the NULL values returned by the query are guaranteed to be substituted by the LEFT JOIN, not fetched out of the actual t_right's row. This means that LEFT JOIN / IS NULL is guaranteed to return at most one row from t_left, and these row's value is not equal to one of those in t_right.
The same holds for NOT EXISTS. Since it's a predicate, not a JOIN condition, the rows from t_left can only be returned at most once too. EXISTS always returns TRUE or FALSE and it will return TRUE as soon as it finds only a single matching row in t_right, or FALSE, if it find none.
NOT EXISTS, therefore, will return TRUE only if no row satisfying the equality condition is found in t_right (same as for LEFT JOIN / IS NULL).
Note that NULL values do not safisfy the equality conditions, so both LEFT JOIN / IS NULL and NOT EXISTS will always return rows from t_left that have value set to NULL, even is there are rows with value IS NULL in t_right.
NOT IN, however, behaves differently.
IN predicate (unlike EXISTS) is trivalent, i. e. it can return TRUE, FALSE or NULL:

TRUE is returned when the non-NULL value in question is found in the list
FALSE is returned when the non-NULL value is not found in the list and the list does not contain NULL values
NULL is returned when the value is NULL, or the non-NULL value is not found in the list and the list contains at least one NULL value

IN predicate does not give a definitive answer to whether or not the expression is contained in the list as long as there are NULL values on either side of the expression, returning NULL instead.
This of course makes no difference when using the positive form of NULL: predicates returning NULL are filtered out by the WHERE clause as well as those returning FALSE.
However, NOT IN is different, since negation of NULL is NULL as well.
That's why NOT IN condition will never hold for any list with a NULL value in it.

If a row is found in the list, IN will return TRUE and NOT IN, therefore, will return FALSE
If a row is not found in the list, IN will return NULL, and NOT IN on its turn will also return NULL

Both conditions will of course be filtered out by the WHERE clause.
Let's illustrate it with two simple queries that compare (1, NULL) in t_left with (2, NULL) in t_right:

WITH    t_left AS

(

SELECT  1 AS value

UNION ALL

SELECT  NULL

),

t_right AS

(

SELECT  2 AS value

UNION ALL

SELECT  NULL

)

SELECT  l.*

FROM    t_left l

WHERE   NOT EXISTS

(

SELECT  NULL

FROM    t_right r

WHERE   r.value = l.value

)

value
1
NULL
2 rows fetched in 0.0001s (0.0006s)

This query, using NOT EXISTS, returns both values from t_left, since neither of them is equal to any of the values from t_right.

WITH    t_left AS

(

SELECT  1 AS value

UNION ALL

SELECT  NULL

),

t_right AS

(

SELECT  2 AS value

UNION ALL

SELECT  NULL

)

SELECT  l.*

FROM    t_left l

WHERE   l.value NOT IN

(

SELECT  value

FROM    t_right

)

value
0 rows fetched in 0.0001s (0.0005s)

This query, on the other hand, returns nothing. Since there is a NULL in t_right, NOT IN returns NULL rather than TRUE if the value is not found among the defined values. Just in case.
IN (and NOT IN) are too chicken to say something definite about lists with NULL unless they are completely sure that the value is there.
However, if the values in both tables are non-nullable, NULL, all three method describe above are semantically identical.

Efficiency comparison

Let's see how efficient are these methods.
To do that, we will create two sample tables:
Table creation details

Table t_left contains 100,000 rows with 10,000 distinct values.
Table t_right contains 1,000,000 rows with 10,000 distinct values.
There are 10 rows in t_left with values not present in t_right.
Let's run the queries against these tables.

NOT IN

SELECT  l.id, l.value

FROM    [20090915_anti].t_left l

WHERE   l.value NOT IN

(

SELECT  value

FROM    [20090915_anti].t_right r

)

View query results, details and execution plan

As we can see, this query uses Merge Anti Semi Join which is extremely efficient if there is a cheap way to obtain two ordered resultsets (like in example above). Since value is indexed in both tables, the indexes serve as such resulsets.
Merge Join means that the server iterates both resultsets from lower values to higher ones, keeping a pointer to the current value and advancing it in both resultsets.
Anti Semi Join above means that as soon as the engine meets a match in t_right it just skips all matching values in both t_left and t_right. Since values from t_right are pregrouped using Stream Aggregate (making the right resultset 100 times as small), the values are only skipped in t_left (10 at once).
The whole query takes as little as 0.271 s.

NOT EXISTS

SELECT  l.id, l.value

FROM    [20090915_anti].t_left l

WHERE   NOT EXISTS

(

SELECT  NULL

FROM    [20090915_anti].t_right r

WHERE   r.value = l.value

)

View query results, details and execution plan

Exactly same plan and exactly same execution time as above.
In SQL Server, NOT IN and NOT EXISTS are complete synonyms in terms of the query plans and execution times (as long as both columns are NOT NULL).

LEFT JOIN / IS NULL

SELECT  l.id, l.value

FROM    [20090915_anti].t_left l

LEFT JOIN

[20090915_anti].t_right r

ON      r.value = l.value

WHERE   r.value IS NULL

View query results, details and execution plan

Here, the results are the same but performance details are very different.
SQL Server's optimizer cannot discern an ANTI JOIN in a LEFT JOIN / IS NULL construct.
That's why it just build the complete resultset (as with a common LEFT JOIN) and filters out the matching values.
Since we have lots of values to filter in this case (almost 10,000,000), it's a hard job to filter such a tremendous lot of values. This operation is performed using quite an efficient Hash Match which can be and is parallelized, but filtering the values out still takes the most time.
That's why the LEFT JOIN / IS NULL query takes 810 ms, or 3 times as much as the NOT EXISTS / NOT IN query.

Summary

In SQL Server, NOT EXISTS and NOT IN predicates are the best way to search for missing values, as long as both columns in question are NOT NULL. They produce the safe efficient plans with some kind of an Anti Join.
LEFT JOIN / IS NULL is less efficient, since it makes no attempt to skip the already matched values in the right table, returning all results and filtering them out instead.

Not in , No exists is null
Written by Quassnoi
September 15th, 2009 at 11:00 pm

Posted in SQL Server

17 Responses to 'NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server'

Subscribe to comments with RSS

Just a note, it looks like a typo in the line above the Summary. It should read “LEFT JOIN / IS NULL” instead of “LEFT JOIN / NOT IN”

dansan
7 Feb 13 at 18:11
Oh, thanks, fixed!

Quassnoi
7 Feb 13 at 18:16

URL'S de consultas y videos

sábado, 25 de abril de 2015

Part 9 Difference between inner join and left join

domingo, 19 de abril de 2015

EJEMPLOS IDENTITY INSERT Y CTE COMMON TABLES EXPRESION

EJEMPLO CTE COMMON TABLE EXPRESIONS TABLAS TEMPORALES

EJEMPLO CTE COMMON TABLE EXPRESIONS

CTE: Common Table Expressions – SQL Server 2008

SQL Server -- Inserting and Deleting Records Tutorial Part 3 of 5 -- I...

EXPLAIN EXTENDED

NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server

Differences between the methods

Efficiency comparison

NOT IN

NOT EXISTS

LEFT JOIN / IS NULL

Summary

17 Responses to 'NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server'

Datos personales

sábado, 25 de abril de 2015

domingo, 19 de abril de 2015

CTE: Common Table Expressions – SQL Server 2008

Differences between the methods

Efficiency comparison

NOT IN

NOT EXISTS

LEFT JOIN / IS NULL

Summary

Share this:

17 Responses to 'NOT IN vs. NOT EXISTS vs. LEFT JOIN / IS NULL: SQL Server'