CONSULTAS BÁSICAS EN TERADATA

Usaremos estas dos tablas: PR_EMPEADO_T & PR_DEPARTAMENTO_T (Un empleado solo puede estar en un departamento y un departamento puede tener 0 o más empleados) Clave de union (department_number) LEFT JOIN; RIGHT JOIN; INNER JOIN, FULL JOIN TODOS LOS EMPLEADOS TENGAN O NO DEPARTAMENTO (Datos de Izquierda y cruce) SELECT employee_number, first_name, department_number FROM BASE_DATOS.PR_EMPLEADO_T ORDER BY department_number,employee_number; employee_number first_name department_number department_name 5.000 Jose. NULL NULL 5.001 Luis NULL NULL 5.002 Adrián NULL NULL 801 I.B. 100 president 1.021 Jim 201 technical operations 1.025 Michael 201 technical operations 1.006 John 301 research and development 1.008 Carol 301 research and development 1.019 Ron 301 research and development 1.016 Nora 302 product planning 1.001 William 401 customer support 1.002 Alan 401 customer support 1.003 James 401 customer support 1.004 Darlene 401 customer support 1.010 Frank 401 customer support 1.013 Charles 401 customer support 1.022 Albert 401 customer support 1.011 James 402 software support 1.014 Robert 402 software support 1.005 Loretta 403 education 1.007 Arnando 403 education 1.009 Domingus 403 education 1.012 Paulene 403 education 1.020 John 403 education 1.024 Allen 403 education 1.015 Edward 501 marketing sales 1.017 Irene 501 marketing sales 1.018 Larry 501 marketing sales 1.023 Peter 501 marketing sales >> Los empleados sin departamento --IMPORTANTE: El LEFT JOIN obtiene todos los empleados y el WHERE filtra. SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E LEFT OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number WHERE D.department_number IS NULL ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name 5.000 Jose. NULL NULL 5.001 Luis NULL NULL 5.002 Adrián NULL NULL -- En este caso el AND lo que hace es no sacar datos de los departamentos, pero obtiene todos los empleados. -- Realmente no tiene sentido. SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E LEFT OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number AND E.department_number IS NULL ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name 5.000 Jose. NULL NULL 5.001 Luis NULL NULL 5.002 Adrián NULL NULL 801 I.B. 100 NULL 1.021 Jim 201 NULL 1.025 Michael 201 NULL 1.006 John 301 NULL 1.008 Carol 301 NULL 1.019 Ron 301 NULL 1.016 Nora 302 NULL 1.001 William 401 NULL 1.002 Alan 401 NULL 1.003 James 401 NULL 1.004 Darlene 401 NULL 1.010 Frank 401 NULL 1.013 Charles 401 NULL 1.022 Albert 401 NULL 1.011 James 402 NULL 1.014 Robert 402 NULL 1.005 Loretta 403 NULL 1.007 Arnando 403 NULL 1.009 Domingus 403 NULL 1.012 Paulene 403 NULL 1.020 John 403 NULL 1.024 Allen 403 NULL 1.015 Edward 501 NULL 1.017 Irene 501 NULL 1.018 Larry 501 NULL 1.023 Peter 501 NULL -- De todos los empleados obtienes los datos del departamentoe 501 . Sigue sin tener demasiado sentido. SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E LEFT OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number AND E.department_number =501 ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name 801 I.B. 100 NULL 1.001 William 401 NULL 1.002 Alan 401 NULL 1.003 James 401 NULL 1.004 Darlene 401 NULL 1.005 Loretta 403 NULL 1.006 John 301 NULL 1.007 Arnando 403 NULL 1.008 Carol 301 NULL 1.009 Domingus 403 NULL 1.010 Frank 401 NULL 1.011 James 402 NULL 1.012 Paulene 403 NULL 1.013 Charles 401 NULL 1.014 Robert 402 NULL 1.015 Edward 501 marketing sales 1.016 Nora 302 NULL 1.017 Irene 501 marketing sales 1.018 Larry 501 marketing sales 1.019 Ron 301 NULL 1.020 John 403 NULL 1.021 Jim 201 NULL 1.022 Albert 401 NULL 1.023 Peter 501 marketing sales 1.024 Allen 403 NULL 1.025 Michael 201 NULL 5.000 Jose. NULL NULL 5.001 Luis NULL NULL 5.002 Adrián NULL NULL -- De todos los empleados obtienes los datos del departamento 501 . Es lo más correcto. SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E LEFT OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number WHERE E.department_number =501 ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name 1.015 Edward 501 marketing sales 1.017 Irene 501 marketing sales 1.018 Larry 501 marketing sales 1.023 Peter 501 marketing sales Por tanto, siempre hay que tener en cuenta que dentro de los filtros en los LEFT/RIGHT, las condiciones de filtro lo que hacen es obtener de la tabla principal todos los datos sin que le afecte el cruce con otras tablas. Los filtros 'ON' son para filtrar los datos de esta segunda tabla. Las condiciones en el WHERE sí filtran los datos de la tabla principal. SOLO LOS EMPLEADOS QUE NO TIENEN DEPARTAMENTO (Datos de la Izquierda) SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E LEFT OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number WHERE D.department_number IS NULL ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name 5.000 Jose. NULL NULL 5.001 Luis NULL NULL 5.002 Adrián NULL NULL DEPARTAMENTOS TENGAN O NO EMPLEADOS (Datos: Derecha y cruce) SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E RIGHT OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name NULL NULL NULL None 801 I.B. 100 president 1.021 Jim 201 technical operations 1.025 Michael 201 technical operations 1.006 John 301 research and development 1.008 Carol 301 research and development 1.019 Ron 301 research and development 1.016 Nora 302 product planning 1.001 William 401 customer support 1.002 Alan 401 customer support 1.003 James 401 customer support 1.004 Darlene 401 customer support 1.010 Frank 401 customer support 1.013 Charles 401 customer support 1.022 Albert 401 customer support 1.011 James 402 software support 1.014 Robert 402 software support 1.005 Loretta 403 education 1.007 Arnando 403 education 1.009 Domingus 403 education 1.012 Paulene 403 education 1.020 John 403 education 1.024 Allen 403 education 1.015 Edward 501 marketing sales 1.017 Irene 501 marketing sales 1.018 Larry 501 marketing sales 1.023 Peter 501 marketing sales DEPARTAMENTOS QUE NO TENGAN EMPLEADOS (Datos: Derecha ) SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E RIGHT OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number WHERE E.department_number IS NULL ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name NULL NULL NULL None EMPLEADOS Y SUS DEPARTAMENTOS (Datos: Cruce ) SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E INNER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name 801 I.B. 100 president 1.021 Jim 201 technical operations 1.025 Michael 201 technical operations 1.006 John 301 research and development 1.008 Carol 301 research and development 1.019 Ron 301 research and development 1.016 Nora 302 product planning 1.001 William 401 customer support 1.002 Alan 401 customer support 1.003 James 401 customer support 1.004 Darlene 401 customer support 1.010 Frank 401 customer support 1.013 Charles 401 customer support 1.022 Albert 401 customer support 1.011 James 402 software support 1.014 Robert 402 software support 1.005 Loretta 403 education 1.007 Arnando 403 education 1.009 Domingus 403 education 1.012 Paulene 403 education 1.020 John 403 education 1.024 Allen 403 education 1.015 Edward 501 marketing sales 1.017 Irene 501 marketing sales 1.018 Larry 501 marketing sales 1.023 Peter 501 marketing sales EMPLEADOS SIN DEPARTAMENTO Y DEPARTAMENTOS SIN EMPLEADOS (Datos:Izquierda y derecha ) SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E FULL OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number WHERE (E.department_number IS NULL OR D.department_number IS NULL) ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name NULL NULL NULL None 5.000 Jose. NULL NULL 5.001 Luis NULL NULL 5.002 Adrián NULL NULL TODOS LOS EMPLEADOS Y TODOS LOS DEPARTAMENTOS (Datos:Izquierda, derecha y cruce. Todos. ) SELECT E.employee_number,E. first_name, E.department_number,D.department_name FROM BASE_DATOS.PR_EMPLEADO_T E FULL OUTER JOIN BASE_DATOS.PR_DEPARTAMENTO_T D ON E.department_number=D.department_number ORDER BY E.department_number, employee_number; employee_number first_name department_number department_name NULL NULL NULL None 5.000 Jose. NULL NULL 5.001 Luis NULL NULL 5.002 Adrián NULL NULL 801 I.B. 100 president 1.021 Jim 201 technical operations 1.025 Michael 201 technical operations 1.006 John 301 research and development 1.008 Carol 301 research and development 1.019 Ron 301 research and development 1.016 Nora 302 product planning 1.001 William 401 customer support 1.002 Alan 401 customer support 1.003 James 401 customer support 1.004 Darlene 401 customer support 1.010 Frank 401 customer support 1.013 Charles 401 customer support 1.022 Albert 401 customer support 1.011 James 402 software support 1.014 Robert 402 software support 1.005 Loretta 403 education 1.007 Arnando 403 education 1.009 Domingus 403 education 1.012 Paulene 403 education 1.020 John 403 education 1.024 Allen 403 education 1.015 Edward 501 marketing sales 1.017 Irene 501 marketing sales 1.018 Larry 501 marketing sales 1.023 Peter 501 marketing sales