PDF de programación - Ampliación de sql

Universidade da Coruña

Departamento de Computación

Ampliación de SQL

Luis A. González Ares

[email protected]

HACLUCE Ampliación de SQL – Planteamiento

SQL.

Objetivos
• Describir algunos de los elementos que más recientemente se han incorporado al lenguaje
• Analizar las características de dichos elementos.
• Conocer su implementación en un SGBD real.
• Establecer relaciones entre los nuevos conocimientos de SQL y los que ya se revisaron.

Contenidos

1. Elementos iniciales
2. Join
3. Otros elementos
4. Expresiones condicionales

Nota

El presente material es un resumen de lo impartido en las clases de la Facultad de Informática,
que se entrega como documento de apoyo.

Ampliación de SQL – Luis Glez. Ares – [email protected]

1

Ampliación de SQL – Elementos iniciales

Caracteres problemáticos

’ Cadenas de caracteres que contienen un apóstrofe. Aparición en INSERT, SELECT, etc.
Solución: ’’ (su aparición dos veces lo convierte en el propio carácter, y no en lo que
representa).

Predicado LIKE

Correspondencia con un patrón o modelo. Comodines:

%

expresión_carácter [NOT] LIKE

patrón

[ESCAPE carácter_protector]

Problemas si el patrón contiene comodines. Solución: indicar un carácter protector.

SELECT
FROM
WHERE
ORDER BY nombre

*
emp
ename LIKE ’%a%%’ ESCAPE ’a’

Predicado SIMILAR

Correspondencia con un patrón (expresión regular).

Usa:

_

% * +

[] [^ ] [: :]

| || ()

expresión_carácter [NOT] SIMILAR

patrón

Ampliación de SQL – Luis Glez. Ares – [email protected]

2

Ampliación de SQL – Elementos iniciales

(ii)

Predicado EXISTS

Comprobación de si una subconsulta devuelve o no filas.

... [NOT] EXISTS

subconsulta

SELECT
FROM
WHERE

*
dept d
EXISTS (SELECT *

emp

FROM
WHERE sal > 1500
AND

deptno = d.deptno)

Predicado UNIQUE

Comprueba si en el resultado de una subconsulta aparecen filas repetidas.

... [NOT] UNIQUE

subconsulta

SELECT
FROM
WHERE

*
dept d
UNIQUE (SELECT *

emp

FROM
WHERE sal > 1500
AND

deptno = d.deptno)

Ampliación de SQL – Luis Glez. Ares – [email protected]

3

Ampliación de SQL – Elementos iniciales

(iii)

Operadores de cuantificación ANY o SOME, ALL

Comprobación de una expresión escalar con el resultado del operador sobre una subconsulta.

... expresión_escalar operador_comparación { ALL | SOME | ANY } (lista_valores)

Operadores de comparación:

=

!=

> <

<= >=

SELECT *
FROM
WHERE sal = ANY (SELECT sal
emp

emp

FROM
WHERE deptno = 30);

Operadores conjuntistas UNION, INTERSECT, EXCEPT

Aplica el operador al resultado de dos consultas.

consulta1
{ UNION | EXCEPT | INTERSECT }
{ ALL | DISTINCT }
consulta2

• UNION: Unión.
• INTERSECT: Intersección.
• EXCEPT: Diferencia.

Ampliación de SQL – Luis Glez. Ares – [email protected]

4

Ampliación de SQL – Elementos iniciales

(iv)

Operadores conjuntistas UNION, INTERSECT, EXCEPT (cont.)

Cada operador por defecto aplica DISTINCT.

• UNION: Valores que aparecen en las filas de una tabla o de la otra, sin repetir ningún valor.

• UNION ALL: Valores que aparecen en las filas de una tabla o de la otra, repetidos las veces

que aparecen.

• INTERSECT: Valores que aparecen en las filas de una tabla y de la otra, sin repetir ningún

valor.

• INTERSECT ALL: Valores que aparecen en las filas de una tabla y de la otra, repetidos n
veces (si x e y son el número de veces que se repite un valor en la primera y en la segunda
tabla, respectivamente, entonces n = mín(x, y)).

• EXCEPT: Valores que aparecen en las filas de la primera tabla y no en la segunda, sin repetir

ningún valor.

• EXCEPT ALL: Valores que aparecen en las filas de la primera tabla y no en la segunda,
repetidos n veces (si x e y son el número de veces que se repite un valor en la primera y
en la segunda tabla, respectivamente, entonces n = máx(x − y, 0)).

Ampliación de SQL – Luis Glez. Ares – [email protected]

5

Ampliación de SQL – Join

INNER JOIN
Permite vincular las columnas de varias tablas mediante operadores de comparación.
Sintaxis:

SELECT *
FROM
ON

t1 [INNER] JOIN t2
condición_join

Dados:

emp(empno, ename, deptno)

dept(deptno, dname, loc)

el join se expresa mediante:

SELECT *
FROM
WHERE emp.deptno = dept.deptno
AND

ename LIKE ’A%’

emp, dept

SELECT *
FROM
ON

emp INNER JOIN dept
emp.deptno = dept.deptno

WHERE ename LIKE ’A%’

Supongamos ahora:

emp(empno, ename, deptno, loc)

dept(deptno, dname, loc)

y deseamos (equijoin):

SELECT *
FROM
ON
AND emp.loc = dept.loc

emp INNER JOIN dept
emp.deptno = dept.deptno

Ampliación de SQL – Luis Glez. Ares – [email protected]

6

Ampliación de SQL – Join

(ii)

Join natural
Si expresamos una condición de igualdad sobre todas las columnas que tienen el mismo
nombre en dos tablas, se tiene el join natural.

Sintaxis:

SELECT *
FROM

t1 NATURAL [INNER] JOIN t2

Dados:

emp(empno, ename, deptno, loc)

dept(deptno, dname, loc)

el join natural sería:

Una alternativa de efectos casi iguales:

SELECT *
FROM

emp NATURAL INNER JOIN dept

SELECT *
FROM

emp INNER JOIN dept

USING (deptno, loc)

El join normal:

SELECT *
FROM
ON
AND emp.loc = dept.loc

emp INNER JOIN dept
emp.deptno = dept.deptno

Los resultados son muy parecidos: en el primer y segundo resultado, las columnas deptno y
loc aparecen una única vez; no así en el tercero. El join natural y el join con USING son iguales
del todo sólo si todas las columnas de nombres iguales están en el USING.

El join natural se corresponde con la operación join () de álgebra relacional.

Ampliación de SQL – Luis Glez. Ares – [email protected]

7

Ampliación de SQL – Join

(iii)

OUTER JOIN

Permite vincular las columnas de varias tablas mediante operadores de comparación, haciendo
que aparezcan todas las columnas de una tabla, de la otra, o de ambas, cumplan o no la
condición de join.

Sean dos relaciones R y S. Tenemos las operaciones:
• Left outer join de R y S (R 1 S): Todas las filas de la relación de la izquierda (R), rellenando

con nulos en las filas que no se correspondan con las de S.

• Right outer join de R y S (R 2 S): Todas las filas de la relación de la derecha (S), rellenando

con nulos en las filas que no se correspondan con las de R.

• Full outer join de R y S (R 3 S): Todas las filas de la relación de la izquierda (R), y
todas las filas de la relación de la derecha (S), rellenando con nulos en las filas que no se
correspondan con las de la otra.

SELECT *
FROM
ON

r {LEFT | RIGHT | FULL} [OUTER] JOIN s
condición_join

SELECT *
FROM
ON

r LEFT OUTER JOIN s
r.c2 = s.c2

Ampliación de SQL – Luis Glez. Ares – [email protected]

8

Ampliación de SQL – Join

(iv)

CROSS JOIN

Representa el producto cartesiano de las tablas involucradas.

SELECT *
FROM

t1 CROSS JOIN t2

SELECT *
FROM

t1, t2

UNION JOIN

Dadas dos tablas, se construye una tabla que tiene cada columna y cada fila de ambas tablas.

SELECT *
FROM

t1 UNION JOIN t2

Dadas las tablas R(a, b) y S(x, y, z), el resultado del UNION JOIN es:

a

|

...
...
...
NULL
...
NULL

b
...

| x

|

y

|

z

NULL

NULL

NULL

...
NULL

NULL
...

NULL
...

NULL
...

NULL

...

...

...

Ampliación de SQL – Luis Glez. Ares – [email protected]

9

Ampliación de SQL – Join

(v)

Múltiples joins

El orden de ejecución es de izquierda a derecha (asociativos a la izquierda).

SELECT expresion, ...
FROM (t1

JOIN t2

ON condición_join1)
JOIN t3
ON condición_join2
...

WHERE predicado

Equivalencias:

SELECT expresion, ...
FROM t1

JOIN t2

ON condición_join1
JOIN t3
ON condición_join2
...

WHERE predicado

Modificación del orden:

SELECT expresion, ...
FROM t1

JOIN

(t2 JOIN tb3
ON condición_join2)
ON condición_join1
...

WHERE predicado

Ampliación de SQL – Luis Glez. Ares – [email protected]

10

Ampliación de SQL – Otros elementos

Subconsultas de fila

Se trata de subconsultas que devuelven más de una columna.

Permite realizar operaciones de comparación simultáneamente sobre cada columna que apa-
rece en su resultado.

Sintaxis:

SELECT expresióna, ...
FROM tabla1, ...
WHERE (expresiónp1, ..., expresiónpn) operador (SELECT expresións1, ..., expresiónsn

FROM ...
WHERE ...)

Operadores válidos: comparación, IN, = SOME, >ALL, ...

Operadores válidos en Oracle 9.2: IN, = SOME, = ALL (para subconsultas de fila!)

Ejemplo:

SELECT *
FROM
WHERE (cd_articulo, precio_min) IN

articulo

(SELECT cd_articulo, precio

FROM

ventas)

Ampliación de SQL – Luis Glez. Ares – [email protected]

11

Ampliación de SQL – Otros elementos

(ii)

Expresión de consulta

Se denomina expresión de consulta, vista en línea o tabla derivada a la utilización de una
consulta en las cláusulas SELECT o FROM de otra.

Sintaxis:

SELECT expresión, ..., (SELECT ...)
FROM
...

t1, ...

SELECT expresión, ...
FROM
...

t1, (SELECT ...)

diferentes.

• No debe confundirse con la denominación tradicional de subconsulta.
• Permite que en el resultado de una consulta aparezcan datos correspondientes a elementos
• Si aparece en la cláusula SELEC sólo puede ser un SELECT escalar.
• Su potencialidad está en que aparezca en la cláusula FROM.
• Las expresiones de consulta dentro de un FROM deben ser autosuficientes.

Ejemplos:

SELECT empno, ename, sal, (SELECT MAX(sal) FROM emp) - sal
FROM

emp

SELECT empno, ename, sal, max_sal, min_sal, max_sal - sal, sal - min_sal
FROM

emp, (SELECT MAX(sal) max_sal, MIN(sal) min_sal

emp)
WHERE sal > max_sal / 2

FROM

Ampliación de SQL – Luis Glez. Ares – [email protected]

12

Ampliación de SQL – Expresiones condicionales

Expresión CASE

En SQL pueden incluirse expresiones condicionales, o sea, que su valor dependa de que se
cumplan unas condiciones, mediante la expresión CASE.

Sintaxis:

CASE

WHEN condición1 THEN expresión1
...
WHEN condiciónn THEN expresiónn
[ELSE expresiónm]

END

Si se produce la condición condicióni el resultado será la expresión expresióni, con i = 1, . . . , n;
en otro caso será expresiónm.
Todas las expresiones deben tener el mismo tipo de datos.

Puede aparecer donde lo pueda hacer una expresi´