Baixe o app para aproveitar ainda mais
Prévia do material em texto
Capítulo 5 Combinando tabelas: produto➪seleção➪projeção Este capítulo trata de consultas que envolvem mais de uma tabela ou fonte de registros. A combinação de tabelas é genericamente denominada junção no jargão relacional e corresponde às operações de produto, seleção e projeção da álgebra relacional. 5.1 TIPOS DE JUNÇÕES Há três categorias básicas de junção: • Cross Join • Inner Join • Outer Join, que abrange três subtipos: Left outer join Right outer join Full outer join CROSS JOIN A junção cross join, entre duas tabelas (ou fontes de registros) R e S, combina todos os registros de R com todos os registros de S. De fato, ela é equivalente à operação de produto da álgebra relacional, apresentada no capítulo 3. Embora a junção cross join raramente seja utilizada isoladamente, é importante compreender seu funcionamento pois ela é a base das demais categorias de junções. Potencialmente, toda consulta SQL que referencia mais de uma fonte de registros realiza uma operação de cross join. Observe esta consulta, que produz as combinações de cada um dos registros da tabela ALUNO com cada um dos registros da tabela CURSO. select * from aluno, curso matricula nome sexo aluno.codcurso nascimento curso.codcurso curso 1001 Ricardo Biondi M DIR 21/02/80 DIR Direito 1001 Ricardo Biondi M DIR 21/02/80 JOR Jornalismo 1001 Ricardo Biondi M DIR 21/02/80 INF Informática 1002 Maria Rita Colatti F INF 10/11/78 DIR Direito 1002 Maria Rita Colatti F INF 10/11/78 JOR Jornalismo 1002 Maria Rita Colatti F INF 10/11/78 INF Informática 1004 Oscarito Vianna M DIR 14/08/79 DIR Direito 1004 Oscarito Vianna M DIR 14/08/79 JOR Jornalismo 68 Combinando tabelas: produto ⇒ seleção ⇒ projeção 1004 Oscarito Vianna M DIR 14/08/79 INF Informática 1005 Barbara Carlito F JOR 29/10/79 DIR Direito 1005 Barbara Carlito F JOR 29/10/79 JOR Jornalismo 1005 Barbara Carlito F JOR 29/10/79 INF Informática 1007 Carlos Maradona M DIR 15/07/79 DIR Direito 1007 Carlos Maradona M DIR 15/07/79 JOR Jornalismo 1007 Carlos Maradona M DIR 15/07/79 INF Informática 1008 Sacadura Miranda M INF 12/12/81 DIR Direito 1008 Sacadura Miranda M INF 12/12/81 JOR Jornalismo 1008 Sacadura Miranda M INF 12/12/81 INF Informática 1010 Maria Lucia Silva F JOR 10/08/75 DIR Direito 1010 Maria Lucia Silva F JOR 10/08/75 JOR Jornalismo 1010 Maria Lucia Silva F JOR 10/08/75 INF Informática Há 21 linhas no resultado porque foram combinados sete registros da tabela ALUNO com três registros da tabela CURSO (7 × 3 = 21). Se houvesse outras fontes de registros na cláusula from, o produto envolveria todas elas. Por sua vez, a consulta select * from aluno, curso, disciplina produz 7 × 3 × 7 = 147 registros, pois combina alunos, cursos e disciplinas. A cláusula where pode ser livremente combinada com a junção cross join. A consulta abaixo filtra as linhas do produto das duas tabelas. select * from aluno, curso where sexo = 'M' matricula nome sexo aluno.codcurso nascimento curso.codcurso curso 1001 Ricardo Biondi M DIR 21/02/80 DIR Direito 1001 Ricardo Biondi M DIR 21/02/80 JOR Jornalismo 1001 Ricardo Biondi M DIR 21/02/80 INF Informática 1004 Oscarito Vianna M DIR 14/08/79 DIR Direito 1004 Oscarito Vianna M DIR 14/08/79 JOR Jornalismo 1004 Oscarito Vianna M DIR 14/08/79 INF Informática 1007 Carlos Maradona M DIR 15/07/79 DIR Direito 1007 Carlos Maradona M DIR 15/07/79 JOR Jornalismo 1007 Carlos Maradona M DIR 15/07/79 INF Informática 1008 Sacadura Miranda M INF 12/12/81 DIR Direito 1008 Sacadura Miranda M INF 12/12/81 JOR Jornalismo 1008 Sacadura Miranda M INF 12/12/81 INF Informática Note que, mesmo que nem todos atributos sejam mostrados no resultado, o produto da fontes de registros é levado a efeito. matricula nome 1001 Ricardo Biondi 1001 Ricardo Biondi 1001 Ricardo Biondi 1002 Maria Rita Colatti 1002 Maria Rita Colatti 1002 Maria Rita Colatti 1004 Oscarito Vianna 1004 Oscarito Vianna 1004 Oscarito Vianna 1005 Barbara Carlito 1005 Barbara Carlito 1005 Barbara Carlito select matricula, nome from aluno, curso order by matricula Tipos de junções 69 1007 Carlos Maradona 1007 Carlos Maradona 1007 Carlos Maradona 1008 Sacadura Miranda 1008 Sacadura Miranda 1008 Sacadura Miranda 1010 Maria Lucia Silva 1010 Maria Lucia Silva 1010 Maria Lucia Silva Esta consulta ilustra um erro bastante comum de programadores SQL iniciantes, que incluem tabelas desnecessárias na cláusula from e “esquecem-se” de que seus registros participam do produto com as demais fontes de registros. O sintoma mais claro deste erro é a repetição de linhas no resultado, provocada pelo produto com a tabela adicional. No caso, cada aluno aparece três vezes porque este é o número de registros da tabela CURSO, incluída indevidamente na cláusula from. ∴ Algumas implementações SQL aceitam uma sintaxe específica para o cross join. A consulta select * from aluno cross join curso cross join disciplina nas implementações mencionadas é equivalente a select * from aluno, curso, disciplina INNER JOIN Na junção do tipo inner join os registros são combinados segundo algum critério lógico. Normalmente, o projeto de bancos de dados prevê atributos que fazem a ligação entre as diversas tabelas do esquema. Essas ligações, ou elos, são então utilizados como critério de combinação nas consultas SQL. A junção natural, mencionada no capítulo 3, é um caso particular de inner join, pois combina registros que têm atributos comuns com valores correspondentes. Um registro que não tem correspondente na outra tabela não aparece no resultado da junção. A consulta a seguir ilustra este caso. select curso, nome from aluno, curso where aluno.codcurso = curso.codcurso order by curso, nome curso nome Direito Carlos Maradona Direito Oscarito Vianna Direito Ricardo Biondi Informática Maria Rita Colatti Informática Sacadura Miranda Jornalismo Barbara Carlito Jornalismo Maria Lucia Silva O resultado contém atributos das tabelas Curso e Aluno: a coluna curso vem da tabela Curso e a coluna nome, da tabela Aluno. Assim, a cláusula from referencia essas duas tabelas. Aluno Matricula Nome Sexo CodCurso Nascimento Curso CodCurso Curso 70 Combinando tabelas: produto ⇒ seleção ⇒ projeção Pensando em termos da Álgebra Relacional, o problema requer o produto da tabela ALUNO e CURSO seguido de uma seleção onde os atributos codcurso são iguais e de uma projeção sobre as colunas desejadas, nome e curso. No esquema da nossa base de exemplos, podemos observar que há uma ligação entre alunos e cursos, através de um atributo comum, codcurso. O raciocínio com as operações da álgebra aplica-se perfeitamente à consulta SQL acima. Inicialmente, podemos iniciar a construção da consulta por uma forma simples, sem a lista alvo, sem filtragem e sem critério de ordenação. select * from aluno, curso A consulta pode agora ser refinada pela filtragem das combinações que interessam, ou seja, onde o atributo codcurso da tabela ALUNO é igual ao atributo codcurso da tabela CURSO. Como o produto acima abriga duas colunas com o mesmo nome, codcurso, essas foram individualmente diferenciadas por um prefixo que identifica a origem de cada uma. select * from aluno, curso where aluno.codcurso = curso.codcurso Até aqui teríamos a operação de produto, em função do cross join, e a operação de seleção, produzindo matricula nome sexo aluno.codcurso nascimento curso.codcurso curso 1 Ricardo Biondi M DIR 21/02/80 DIR Direito 2 Maria Rita Colatti F INF 10/11/78 INF Informática 4 Oscarito Vianna M DIR 14/08/79 DIR Direito 5 Barbara Carlito F JOR 29/10/79 JOR Jornalismo 7 Carlos Maradona M DIR 15/07/79 DIR Direito 8 Sacadura Miranda M INF 12/12/81 INF Informática 10 Maria Lucia Silva F JOR 10/08/75 JOR Jornalismo Agora, aparecem no resultado somente as combinações válidas de alunos e cursos, pois o elode ligação entre as tabelas foi empregado no critério de filtragem. Para chegarmos à forma original da consulta, basta introduzirmos a cláusula select que especifica a lista alvo (a operação de projeção), o critério de ordenação e o resultado desejado é obtido. curso nome Direito Carlos Maradona Direito Oscarito Vianna Direito Ricardo Biondi Informática Maria Rita Colatti Informática Sacadura Miranda Jornalismo Barbara Carlito Jornalismo Maria Lucia Silva select curso, nome from aluno, curso where aluno.codcurso = curso.codcurso Consultas podem combinar mais de duas tabelas, simplesmente pela adição de novas fontes de registros na cláusula from. Vamos considerar uma consulta que produz a lista de alunos e as disciplinas por eles cursadas, de acordo com os elos entre as tabelas da base exemplo. Tipos de junções 71 Disciplina Curso Inscrição Professor 1 1 1 1 0:n 0:n 0:n 0:n Aluno Matrícula Nome Sexo CodCurso Nascimento CodDisciplina Disciplina CodProfessor CHST CHSP CodCurso Curso Matrícula CodDisciplina Nota CodProfessor Nome O relacionamento entre alunos e disciplinas é estabelecido através das inscrições, e a consulta deve combinar os registros das três tabelas na construção do resultado. Note que nesta consulta, todos os atributos foram qualificados, mesmo os que não são ambíguos. Os registros da tabela INSCRIÇÃO servem como ponte, não contribuindo com atributos para o resultado final. O critério de filtragem, contudo, deve selecionar as combinações entre alunos e inscrições, através do termo lógico select aluno.nome, disciplina.disciplina from aluno, disciplina, inscricao where aluno.matricula = inscricao.matricula and inscricao.coddisciplina = disciplina.coddisciplina order by aluno.nome, disciplina.disciplina nome disciplina Barbara Carlito Estatística Barbara Carlito Sociologia Carlos Maradona Dir. Constitucional Carlos Maradona Direito Civil Maria Lucia Silva Português Maria Lucia Silva Sociologia Maria Rita Colatti Compiladores Ricardo Biondi Dir. Constitucional Ricardo Biondi Português aluno.matricula = inscricao.matricula e as combinações entre inscrições e disciplinas, através do termo lógico inscricao.coddisciplina = disciplina.coddisciplina OUTER JOIN: LEFT, RIGHT E FULL Na junção do tipo outer join os registros também devem ser combinados segundo algum critério lógico, com vimos na junção inner join. A diferença é que mesmo os registros que não têm correspondentes na outra tabela podem aparecer no resultado da operação. Esse registros sem correspondentes são combinados com um registro fictício, totalmente preenchido com nulos. Dessa forma, todos os registros de um operando aparecem no resultado, seja combinados com seus correspondentes, seja combinados com o registro de nulos. Se os registros do operando da esquerda são forçados no resultado, a junção é dita um left outer join; se forem forçados os registros do operando da direita, a operação é um right outer join; se forem forçados registros de ambos os operandos, a operação é dita um full outer join. Para ilustrar, vamos considerar um exemplo onde a lista de alunos e suas notas é requerida. O resultado contém os alunos que estão inscritos em alguma disciplina, juntamente com o código de cada disciplina e sua nota. Somente alunos que tenham alguma inscrição aparecem porque a cláusula where requer que haja dois registros, um na tabela ALUNO e outro na tabela INSCRIÇÃO, select aluno.nome, inscricao.coddisciplina, inscricao.nota from aluno, inscricao where aluno.matricula = inscricao.matricula order by aluno.nome, inscricao.coddisciplina nome coddisciplina nota Barbara Carlito 117 4,2 Barbara Carlito 316 3,0 Carlos Maradona 112 NULL Carlos Maradona 114 7,0 Maria Lucia Silva 316 10,0 Maria Lucia Silva 317 5,5 Maria Rita Colatti 210 9,5 Ricardo Biondi 112 NULL Ricardo Biondi 317 8,0 72 Combinando tabelas: produto ⇒ seleção ⇒ projeção que tenham matrículas correspondentes. Vamos supor, entretanto, que os alunos que não possuem inscrições devem ser listados mesmo assim. Uma formulação adequada para a consulta seria select aluno.nome, inscricao.coddisciplina, inscricao.nota from aluno, inscricao where aluno.matricula = inscricao.matricula (+) order by aluno.nome, inscricao.coddisciplina select aluno.nome, inscricao.coddisciplina, inscricao.nota from aluno left join inscricao on aluno.matricula = inscricao.matricula order by aluno.nome, inscricao.coddisciplina nome coddisciplina nota Barbara Carlito 117 4,20 Barbara Carlito 316 3,00 Carlos Maradona 112 NULL Carlos Maradona 114 7,00 Maria Lucia Silva 316 10,00 Maria Lucia Silva 317 5,50 Maria Rita Colatti 210 9,50 Oscarito Vianna NULL NULL Ricardo Biondi 112 NULL Ricardo Biondi 317 8,00 Sacadura Miranda NULL NULL O emprego do left outer join força a inclusão de mais dois alunos que, por não terem inscrição alguma, não apareceram anteriormente no resultado. A idéia é que os registros que não tenham correspondentes apareçam uma vez, combinados com um registro fictício, preenchido com nulos. Oscarito Vianna e Sacadura Miranda não têm inscrições e, nas linhas onde aparecem, os valores dos atributos oriundos da tabela INSCRIÇÃO são nulos. O exemplo acima mostra duas formas sintáticas distintas. No Access, MySQL e SQL Server, a junção left outer join é explicitamente declarada na cláusula from. No Oracle, o left outer join é simbolizado pelo operador (+) conjugado ao sinal de igualdade, significando que a fonte de registros é completada com registros de nulos. As variações sintáticas de cada implementação SQL são detalhadas mais adiante neste capítulo. A diferença entre right outer join e left outer join é meramente posicional. A mesma consulta, com resultado idêntico, poderia ter sido escrita como select aluno.nome, inscricao.coddisciplina, inscricao.nota from inscricao right join aluno on aluno.matricula = inscricao.matricula order by aluno.nome, inscricao.coddisciplina select aluno.nome, inscricao.coddisciplina, inscricao.nota from aluno, inscricao where inscricao.matricula (+)= aluno.matricula order by aluno.nome, inscricao.coddisciplina Aqui, a cláusula from inscricao right join aluno on aluno.matricula = inscricao.matricula especifica que os registros da tabela ALUNO, que aparece à direita na cláusula, que não têm correspondentes devem ser combinados com valores nulos. O uso de right join compensa a inversão da ordem das fontes de registros, INSCRIÇÃO seguida de ALUNO. Entendendo a construção do resultado 73 JUNÇÕES DECLARADAS As junções declaradas empregadas nas consultas acima aderem à sintaxe SQL-92. Nas versões mais antigas do SQL, as operações de outer join eram especificadas no interior da cláusula where, como é o caso da sintaxe Oracle e de um caso particular do SQL Server, comentados mais adiante neste capítulo. Entretanto, a declaração da junção externa fica mais compreensível quando declarada diretamente na cláusula from. No padrão SQL-92, junções do tipo inner join ainda podem ser especificadas na cláusula where, como vimos nos exemplos acima. A sintaxe de junções declaradas especifica os tipos de junções entre pares de fontes de registros. Uma junção é definida por alguma das seguintes combinações cross join inner join on <condição de junção> left outer join on <condição de junção> right outer join on <condição de junção> full outer join on <condição de junção> Cada par de fontes de registros envolvidos numa junção declarada formam uma pseudo fonte de registros que pode, por sua vez, ser referenciada em outra junção declarada. Observe as consultas seguir. select a.nome, d.disciplina from aluno as a cross join inscricao as i cross join disciplinaas d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a inner join inscricao as i on a.matricula = i.matricula inner join disciplina as d on i.coddisciplina = d.coddisciplina Note que, na primeira consulta, o resultado do cross join das tabelas ALUNO e INSCRIÇÃO é utilizado num segundo cross join com a tabela DISCIPLINA. Na segunda consulta, o resultado do inner join (natural) das tabelas ALUNO e INSCRIÇÃO junta-se à tabela DISCIPLINA num segundo inner join. Nem todas as combinações de junções declaradas são permitidas ou suportadas por todas as implementações SQL. As possibilidades de cada implementação serão analisadas mais adiante neste capítulo. 5.2 ENTENDENDO A CONSTRUÇÃO DO RESULTADO Uma dificuldade que surge com freqüência na construção de consultas é a correta compreensão de como o resultado é construído. Dada a expressão de uma consulta, como pode o programador SQL analisá-la e prever seu resultado? Esta seção apresenta um roteiro para inferir a feitura do resultado de uma consulta SQL, com base no conceito de variáveis SQL. VARIÁVEIS DE REGISTRO SQL Toda fonte de registros referenciada na cláusula from tem a ela associada uma variável de registro, implícita ou explicitamente declarada. Vamos tomar como base a consulta apresentada num dos exemplos anteriores. select aluno.nome, inscricao.coddisciplina, inscricao.nota from aluno, inscricao where aluno.matricula = inscricao.matricula order by aluno.nome, inscricao.coddisciplina Aluno e inscrição são variáveis de registro implicitamente declaradas, que herdam seus nomes das fontes de registros correspondentes. Note que os atributos, ambíguos ou não, aparecem prefixados com os nomes dessas variáveis, aluno e inscricao, designando a origem de cada um deles. 74 Combinando tabelas: produto ⇒ seleção ⇒ projeção A mesma consulta, com variáveis explicitamente declaradas e nomeadas, poderia ter sido escrita como select a.nome, i.coddisciplina, i.nota from aluno a, inscricao i where a.matricula = i.matricula order by a.nome, i.coddisciplina produzindo resultado idêntico e sendo equivalente à que foi apresentada anteriormente. As variáveis que representam os registros das tabelas ALUNO e INSCRIÇÃO são explicitamente declaradas e denominadas a e i, respectivamente, na cláusula from. Em todo o restante da consulta, como nas cláusulas where e order by, as referências aos atributos passam as utilizar esses nomes. Variáveis explícitas são um recurso intensamente utilizado por programadores SQL, porque abreviam a codificação das consultas e facilitam sua leitura. Observe que, na declaração implícita, a consulta equivale à construção select aluno.nome, inscricao.coddisciplina, inscricao.nota from aluno aluno, inscricao inscricao where aluno.matricula = inscricao.matricula order by aluno.nome, inscricao.coddisciplina onde as variáveis de registro receberiam nomes idênticos aos de suas fontes de registros. Como regra geral vale que • toda fonte de registros tem uma variável de registro a ela associada; • uma variável de registro pode ser implícita ou explicitamente definida quando sua fonte de registros é declarada na cláusula from. A consulta abaixo é um exemplo em que uma fonte de registros tem uma variável de registro explicitamente declarada e a outra, não. select a.nome, inscricao.coddisciplina, inscricao.nota from aluno a, inscricao inscricao where a.matricula = inscricao.matricula order by a.nome, inscricao.coddisciplina CONSTRUINDO O RESULTADO Uma variável de registro pode ser instanciada por qualquer dos registros da sua fonte correspondente. Para cada combinação das diferentes instâncias das variáveis de registro de uma consulta, uma linha é construída e adicionada ao resultado se o critério de filtragem da cláusula where for verdadeiro. Vamos tomar como exemplo a consulta select a.nome, d.disciplina from aluno a, disciplina d, inscricao i where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina order by a.nome, d.disciplina com as variáveis de registro explícitas a, i e d associadas às tabelas ALUNO, INSCRIÇÃO e DISCIPLINA, respectivamente. O quadro a seguir mostra as possíveis instâncias para a variável a. Os registros de ALUNO foram numerados de 1 até 7, e cada um deles é uma potencial instância de a. 1 2 3 4 5 6 7 . . . . . . a ALUNO Matricula Nome Sexo CodCurso Nascimento 1001 Ricardo Biondi M DIR 21/02/1980 1002 Maria Rita Colatti F INF 10/11/1978 1004 Oscarito Vianna M DIR 14/08/1979 1005 Barbara Carlito F JOR 29/10/1979 1007 Carlos Maradona M DIR 30/06/1977 1008 Sacadura Miranda M INF 12/12/1981 1010 Maria Lucia Silva F JOR 10/08/1975 Entendendo a construção do resultado 75 O mesmo ocorre para as variáveis d e i, cujas possíveis instâncias aparecem ilustradas nos quadros a seguir. d 1 2 3 4 5 6 7 . . . . . . DISCIPLINA CodDisciplina Disciplina CodProfessor CHST CHSP 112 Dir. 3 4 0 114 Direito 10 4 2 117 Estatística 15 2 2 210 Compilado 2 2 4 211 Bancos de 17 3 3 316 Sociologia 18 3 1 317 Português 12 4 0 1 2 3 4 5 6 7 8 9 . . . . . . . . i INSCRIÇÃO Matricula CodDisciplina Nota 1001 112 NULL 1001 317 8,0 1002 210 9,5 1005 316 3,0 1005 117 4,2 1007 112 NULL 1007 114 7,0 1010 317 5,5 1010 316 10,0 Dentre as combinações das instâncias das variáveis a, d, i que são possíveis, algumas das que produzem as linhas do resultado aparecem assinaladas abaixo. a i d a.matricula i.matricula i.coddisciplina d.coddisciplina 1 1 1 ✓ 1001 1001 112 112 1 1 2 1001 1001 112 114 1 1 3 1001 1001 112 117 1 1 4 1001 1001 112 210 1 1 5 1001 1001 112 211 1 1 6 1001 1001 112 316 1 1 7 1001 1001 112 317 1 2 1 1001 1001 317 112 1 2 2 1001 1001 317 114 1 2 3 1001 1001 317 117 1 2 4 1001 1001 317 210 1 2 5 1001 1001 317 211 1 2 6 1001 1001 317 316 1 2 7 ✓ 1001 1001 317 317 ... ... ... 7 9 1 1010 1010 316 112 7 9 2 1010 1010 316 114 7 9 3 1010 1010 316 117 7 9 4 1010 1010 316 210 7 9 5 1010 1010 316 211 7 9 6 ✓ 1010 1010 316 316 7 9 7 1010 1010 316 317 Quando a instância de a é o primeiro registro de ALUNO, i é o primeiro registro de INSCRIÇÃO e d é o primeiro registro de DISCIPLINA, a expressão lógica da cláusula where é verdadeira porque a.matricula = i.matricula and i.coddisciplina = d.coddisciplina Essa combinação gera então uma linha do resultado, constituída de acordo com a cláusula select select a.nome, d.disciplina 76 Combinando tabelas: produto ⇒ seleção ⇒ projeção a.nome, nesse momento, tem o valor “Ricardo Biondi” e d.disciplina tem o valor “Dir. Constitucional” (note que essa linha não é a primeira no resultado final, uma vez que as linhas são posteriormente ordenadas). A partir daí, as demais combinações são testadas. A próxima combinação é o primeiro registro de ALUNO com o primeiro registro de INSCRIÇÃO com o segundo registro de DISCIPLINA, cujos valores tornam falsa a expressão lógica, e assim sucessivamente. Ao final do ciclo, as combinações que passam pelo critério de filtro são as seguintes. a i d a.matricula i.matricula i.coddisciplina d.coddisciplina 1 1 1 ✓ 1001 1001 112 112 1 2 7 ✓ 1001 1001 317 317 2 3 4 ✓ 1010 1010 210 210 4 4 6 ✓ 1005 1005 316 316 4 5 3 ✓ 1005 1005 117 117 5 6 1 ✓ 1007 1007 112 112 5 7 2 ✓ 1007 1007 114 114 7 8 7 ✓ 1010 1010 317 317 7 9 6 ✓ 1010 1010 316 316 Finalmente, ao lado de cada linha do resultado já ordenado, podemos conferir os números dos registros de cada tabela que, combinados, a originaram. nome disciplina Barbara Carlito Estatística Barbara Carlito Sociologia Carlos Maradona Dir. Constitucional Carlos Maradona Direito Civil Maria Lucia Silva Português Maria Lucia SilvaSociologia Maria Rita Colatti Compiladores Ricardo Biondi Dir. Constitucional Ricardo Biondi Português a i d 4 4 6 4 5 3 5 6 1 5 7 2 7 8 7 7 9 6 2 3 4 1 1 1 1 2 7 Aqui é possível verificar que a linha que contém Bárbara Carlito, Estatística foi gerada quando a variável a continha o registro 4, a variável i continha o registro 4 e a variável d continha o registro 6. Para a linha Maria Rita Colatti, Compiladores os registros 2, 3, 4 instanciavam as variáveis de registro a, i, d, respectivamente. O mesmo raciocínio aplica-se quando há junções do tipo left, right ou full outer join. Após a avaliação das combinações válidas, os registros afetados pelas junções de outer join que não possuem correspondentes são combinados com registros fictícios preenchidos com nulos, com novas linhas anexadas ao resultado. TIPOS DE JUNÇÕES NO ACCESS Access implementa as operações de cross, inner, left e right join. A operação de cross join obedece à sintaxe básica da cláusula from, onde as fontes de registros aparecem separadas por vírgulas, com ou sem variáveis de registro SQL explicitamente declaradas. A consulta select a.nome, c.curso from aluno as a, curso as c é um exemplo disso. Para a operação de inner join, é possível optar pela combinação do produto (cross join) seguido de seleção, como na consulta Tipos de junções no MySQL 77 select a.nome, c.curso from aluno as a, curso as c where a.codcurso = c.codcurso que tem o mesmo efeito da consulta anterior, ou pela sintaxe de junção declarada, como select a.nome, c.curso from aluno as a inner join curso as c on a.codcurso = c.codcurso As operações de left e rigth outer join seguem a sintaxe de junção declarada. Um exemplo é a consulta select a.nome, i.coddisciplina from aluno as a left join inscricao as i on a.matricula = i.matricula ou sua equivalente select a.nome, i.coddisciplina from inscricao as i right join aluno as a on a.matricula = i.matricula TIPOS DE JUNÇÕES NO MYSQL A implementação do MySQL é a mais pródiga em termos de variantes sintáticas para as operações de junção, e implementa as modalidades cross, inner, left e right join. O cross join, além da sintaxe básica da cláusula from, permite as seguintes variações select a.nome, c.curso from aluno as a, curso as c select a.nome, c.curso from aluno as a cross join curso as c select a.nome, c.curso from aluno as a straight_join curso as c As três consultas acima são equivalentes e produzem absolutamente o mesmo resultado. A construção straight_join serve para indicar ao otimizador de consultas que as fontes de registros devem ser lidas rigorosamente na ordem em que aparecem na junção. A operação de inner join pode ser obtida a partir do produto seguido da seleção das combinações de registros correspondentes, sendo que qualquer das sintaxes de cross join pode ser empregada como base. Veja os exemplos. select a.nome, c.curso from aluno as a, curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a cross join curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a straight_join curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a inner join curso as c on a.codcurso = c.codcurso select a.nome, c.curso from aluno as a inner join curso as c using (codcurso) select a.nome, c.curso from aluno as a natural join curso as c Uma novidade aqui é a cláusula using no caso de natural join. Ao invés de fornecer a expressão lógica que conecta os registros correspondentes, basta indicar quais são os atributos que estabelecem essa correspondência. Assim, em lugar de especificar que a expressão lógica on a.codcurso = c.codcurso é o critério de junção, basta listar os atributos que constituem os elos de junção natural, como using (codcurso) 78 Combinando tabelas: produto ⇒ seleção ⇒ projeção TIPOS DE JUNÇÕES NO ORACLE A implementação Oracle, das que foram aqui analisadas, é a que tem o menor número de variações sintáticas, embora também implemente as operações de cross, inner, left e right join. A operação de cross join obedece à sintaxe básica da cláusula from, onde as fontes de registros são especificadas separadas por vírgulas, com ou sem variáveis SQL explicitamente declaradas. A consulta select a.nome, c.curso from aluno a, curso c é um exemplo disso. ∴ A palavra-chave as, opcional nas demais implementações, não é permitida, causando um erro sintático quando utilizada. ∴ A operação de inner join é obtida somente pela combinação do produto (cross join) seguido de seleção, como na consulta select a.nome, c.curso from aluno a, curso c where a.codcurso = c.codcurso Quanto às operações de left e rigth outer join, sua especificação se dá pela presença do operador (+) em conjunto com o operador de comparação que estabelece o critério de associação entre as tabelas sendo combinadas. Um exemplo é a consulta select a.nome, i.coddisciplina from aluno a, inscricao i where a.matricula = i.matricula (+) Note que qualquer que seja a ordem das fontes de registros na cláusula from, o lado da junção que é completado com registros nulos é especificado diretamente pela posição do operador (+) em relação ao operador de comparação. É como se o sinal positivo indicasse o lado onde os registros com nulos seriam acrescentados. • o operador (+) aparece sempre na cláusula from; (??? TABLE) • quando duas tabelas são combinadas através de mais de uma coluna, o operador (+) deve aparecer em todas elas; • um operador de comparação contendo (+) não pode ser conectado a uma outra expressão lógica pelo operador lógico or; • (+) não pode ser combinado com o operador in; • (+) não pode ser combinado com subconsultas; • se três ou mais tabelas são combinadas, não é permitido que uma tabela possua colunas com (+) em comparações com mais de uma tabela. Ou seja, uma tabela somente pode aparecer em algum tipo de outer join apenas uma vez; • se uma tabela aparece num outer join com o operador (+) e a cláusula where possui algum tipo de seleção, esta seleção deve conter o operador (+). Este caso é melhor ilustrado com um exemplo. A consulta select a.nome, i.coddisciplina from aluno a, inscricao i where a.matricula = i.matricula (+) produz a lista de alunos com suas inscrições, incluindo os alunos sem inscrição alguma. nome coddisciplina Ricardo Biondi 317 Ricardo Biondi 112 Maria Rita Colatti 210 Oscarito Vianna NULL Tipos de junções no SQL Server 79 Barbara Carlito 117 Barbara Carlito 316 Carlos Maradona 114 Carlos Maradona 112 Sacadura Miranda NULL Maria Lucia Silva 316 Maria Lucia Silva 317 Observe a mesma consulta quando são selecionadas apenas disciplinas cujos códigos são maiores que 300. select a.nome, i.coddisciplina from aluno a, inscricao i where a.matricula = i.matricula (+) and i.coddisciplina > 300 O resultado agora apresenta-se como nome coddisciplina Ricardo Biondi 317 Barbara Carlito 316 Maria Lucia Silva 316 Maria Lucia Silva 317 Note que aparecem apenas as disciplinas de códigos superiores a 300. Note ainda que os alunos sem inscrição alguma não mais aparecem. Isto ocorre porque houve uma seleção explícita sobre os registros da tabela INSCRIÇÃO. Para manter no resultado os alunos sem inscrição alguma é preciso explicitar que a seleção sobre a tabela INSCRIÇÃO deve levar em conta o outer join entre as duas tabelas. Assim, a consulta escrita como select a.nome, i.coddisciplina from aluno a, inscricao i where a.matricula = i.matricula (+) and i.coddisciplina (+) > 300 produz a lista de alunos inscritos em disciplinas com códigos superiores a 300, além dos alunos que não tem inscrição alguma. nome coddisciplina Ricardo Biondi 317 Oscarito Vianna NULL Barbara Carlito 316 Sacadura Miranda NULL Maria Lucia Silva 316 Maria Lucia Silva 317 TIPOS DE JUNÇÕES NO SQLSERVER No SQL Server é possível construir consultas com todos os tipos de junção. As operações de cross, inner, left e right join seguem sintaxe bastante semelhante à que é utilizada no Access. No cross join, além da sintaxe básica da cláusula from, o SQL Server aceita uma sintaxe de junção declarada, como na consulta select a.nome, c.curso from aluno as a cross join curso as c Outra diferença, esta pequena, é notada no emprego de left e right outer join, onde a palavra outer é opcional, como nas duas consultas equivalentes, mostradas abaixo. select a.nome, i.coddisciplina from aluno as a left outer join inscricao as i on a.matricula = i.matricula 80 Combinando tabelas: produto ⇒ seleção ⇒ projeção select a.nome, i.coddisciplina from inscricao as i right outer join aluno as a on a.matricula = i.matricula De modo a oferecer compatibilidade com suas versões antigas, o SQL Server aceita os operadores *= e =* na especificação de left e right outer joins. Qualquer das duas consultas acima pode ser escrita como select a.nome, i.coddisciplina from aluno as a, inscricao as i where a.matricula *= i.matricula Entretanto, esta é uma sintaxe ambígua, que deve ser evitada, porque o caracter * pode ser interpretado como o operador aritmético de multiplicação em certos casos. Note que, neste caso, a ordem das tabelas na cláusula from é irrelevante. Quanto à operação de outer full join, sua implementação obedece à sintaxe select a.nome, i.coddisciplina from aluno a full outer join inscricao i on a.matricula = i.matricula 5.3 COMPARANDO AS VARIAÇÕES SINTÁTICAS Esta seção compara as várias alternativas sintáticas disponíveis nos SGBD analisados neste livro. A partir de uma consulta de referência, empregando cada um dos tipos de junção, são apresentadas as diversas construções permitidas em cada implementação SQL. CROSS JOIN DE DUAS TABELAS A primeira consulta de referência deve produzir todas as combinações de nomes de alunos com nomes de curso, obtida através do produto entre as tabelas ALUNO e CURSO, como mostrado abaixo. nome curso Ricardo Biondi Direito Ricardo Biondi Jornalismo Ricardo Biondi Informática Maria Rita Colatti Direito Maria Rita Colatti Jornalismo Maria Rita Colatti Informática Oscarito Vianna Direito Oscarito Vianna Jornalismo Oscarito Vianna Informática Barbara Carlito Direito Barbara Carlito Jornalismo Barbara Carlito Informática Carlos Maradona Direito Carlos Maradona Jornalismo Carlos Maradona Informática Sacadura Miranda Direito Sacadura Miranda Jornalismo Sacadura Miranda Informática Maria Lucia Silva Direito Maria Lucia Silva Jornalismo Maria Lucia Silva Informática Para cada implementação SQL, o quadro a seguir mostra as variações sintáticas que expressam essa mesma consulta de referência. Comparando as variações sintáticas 81 select a.nome, c.curso from aluno as a, curso as c select a.nome, c.curso from aluno as a, curso as c select a.nome, c.curso from aluno as a cross join curso as c select a.nome, c.curso from aluno as a straight_join curso as c select a.nome, c.curso from aluno a, curso c select a.nome, c.curso from aluno as a, curso as c select a.nome, c.curso from aluno as a cross join curso as c CROSS JOIN DE TRÊS TABELAS Um segundo exemplo de cross join utiliza como referência uma consulta que deve produzir todas as possíveis combinações de alunos, cursos e disciplinas. Este resultado contém 147 linhas no formato nome curso disciplina ... ... ... Para cada implementação SQL, o quadro a seguir mostra as variações sintáticas que expressam a mesma consulta básica. select a.nome, c.curso, d.disciplina from aluno as a, curso as c, disciplina as d select a.nome, c.curso, d.disciplina from aluno as a, curso as c, disciplina as d select a.nome, c.curso, d.disciplina from aluno as a cross join curso as c cross join disciplina as d select a.nome, c.curso, d.disciplina from aluno as a straight_join curso as c straight_join disciplina as d select a.nome, c.curso, d.disciplina from aluno a, curso c, disciplina d select a.nome, c.curso, d.disciplina from aluno as a, curso as c, disciplina as d select a.nome, c.curso, d.disciplina from aluno as a cross join curso as c cross join disciplina as d INNER JOIN DE DUAS TABELAS Na comparação da operação de inner join, a consulta de referência produz a lista de nomes de alunos e respectivos cursos. O elo entre as tabelas ALUNO e CURSO é o atributo codcurso, presente em ambas. Note que, neste caso, utilizamos a junção natural entre essas tabelas. 82 Combinando tabelas: produto ⇒ seleção ⇒ projeção nome curso Carlos Maradona Direito Oscarito Vianna Direito Ricardo Biondi Direito Maria Lucia Silva Jornalismo Barbara Carlito Jornalismo Sacadura Miranda Informática Maria Rita Colatti Informática Para cada implementação SQL, o quadro a seguir mostra as variações sintáticas que expressam a consulta de referência. select a.nome, c.curso from aluno as a, curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a inner join curso as c on a.codcurso = c.codcurso select a.nome, c.curso from aluno as a, curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a cross join curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a straight_join curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a inner join curso as c on a.codcurso = c.codcurso select a.nome, c.curso from aluno as a inner join curso as c using (codcurso) select a.nome, c.curso from aluno as a natural join curso as c select a.nome, c.curso from aluno a, curso c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a, curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a cross join curso as c where a.codcurso = c.codcurso select a.nome, c.curso from aluno as a inner join curso as c on a.codcurso = c.codcurso INNER JOIN DE TRÊS TABELAS O segundo exemplo de inner join supõe uma consulta de referência que produz a lista de alunos que têm inscrições, com as respectivas disciplinas nas quais estão inscritos, como é mostrado a seguir. nome disciplina Carlos Maradona Dir. Constitucional Ricardo Biondi Dir. Constitucional Carlos Maradona Direito Civil Barbara Carlito Estatística Maria Rita Colatti Compiladores Maria Lucia Silva Sociologia Barbara Carlito Sociologia Comparando as variações sintáticas 83 Maria Lucia Silva Português Ricardo Biondi Português O quadro a seguir mostra as variações sintáticas para essa consulta. select a.nome, d.disciplina from aluno as a, inscricao as i, disciplina as d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from ( aluno as a inner join inscricao as i on a.matricula = i.matricula ) inner join disciplina as d on i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a, inscricao as i, disciplina as d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a cross join inscricao as i cross join disciplina as d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a straight_join inscricao as i straight_join disciplina as d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a inner join inscricao as i on a.matricula = i.matricula inner join disciplina as d on i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a inner join inscricao as i using (matricula) inner join disciplina as d using (coddisciplina) select a.nome, d.disciplina from( aluno as a natural join inscricao as i ) natural join disciplina as d select a.nome, d.disciplina from aluno a, inscricao i, disciplina d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a, inscricao as i, disciplina as d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a cross join inscricao as i cross join disciplina as d where a.matricula = i.matricula and i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a inner join inscricao as i on a.matricula = i.matricula inner join disciplina as d on i.coddisciplina = d.coddisciplina Note que na segunda consulta Access e na última do MySQL foi necessário introduzir parênteses nas expressões de junção. Do contrário, o Access acusa erro de sintaxe e o MySQL produz resultados errôneos. OUTER JOIN DE DUAS TABELAS: LEFT E RIGHT A operação genérica de outer join pode ser do tipo left outer join, right outer join ou full outer join, dependendo de como os operandos são completados por ocasião da produção das linhas do resultado. A primeira consulta de referência produz a lista de todos os alunos juntamente com os códigos das disciplinas, quando estiverem inscritos em alguma, caracterizando assim um caso de left outer join. nome coddisciplina Ricardo Biondi 317 84 Combinando tabelas: produto ⇒ seleção ⇒ projeção Ricardo Biondi 112 Maria Rita Colatti 210 Oscarito Vianna NULL Barbara Carlito 117 Barbara Carlito 316 Carlos Maradona 114 Carlos Maradona 112 Sacadura Miranda NULL Maria Lucia Silva 316 Maria Lucia Silva 317 As variações sintáticas de cada implementação SQL que expressam essa mesma consulta referência são mostradas a seguir. select a.nome, i.coddisciplina from aluno as a left join inscricao as i on a.matricula = i.matricula select a.nome, i.coddisciplina from aluno as a left outer join inscricao as i on a.matricula = i.matricula select a.nome, i.coddisciplina from aluno as a left outer join inscricao as i using (matricula) select a.nome, i.coddisciplina from aluno a, inscricao i where a.matricula = i.matricula (+) select a.nome, i.coddisciplina from aluno as a left outer join inscricao as i on a.matricula = i.matricula select a.nome, i.coddisciplina from aluno as a, inscricao as i where a.matricula *= i.matricula O quadro a seguir apresenta as variações da consulta de referência, agora construída com as fontes de registros em ordem inversa, caraterizando a operação de right outer join. O resultado é igual ao que foi mostrado acima. select a.nome, i.coddisciplina from inscricao as i right join aluno as a on a.matricula = i.matricula select a.nome, i.coddisciplina from inscricao as i right outer join aluno as a on a.matricula = i.matricula select a.nome, i.coddisciplina from inscricao as i right outer join aluno as a using (matricula) select a.nome, i.coddisciplina from aluno a, inscricao i where i.matricula (+) = a.matricula select a.nome, i.coddisciplina from inscricao as i right outer join aluno as a on a.matricula = i.matricula select a.nome, i.coddisciplina from aluno a, inscricao i where i.matricula =* a.matricula Comparando as variações sintáticas 85 OUTER JOIN DE TRÊS TABELAS: LEFT E RIGHT Neste caso, a consulta de referência deve produzir a lista de todos os alunos e, quando for o caso, os nomes das disciplinas em que estão inscritos. Esta consulta utiliza três fontes de registros, nominalmente as tabelas ALUNO, INSCRIÇÃO e DISCIPLINA, e tem por resultado a lista mostrada a seguir. nome disciplina Ricardo Biondi Português Ricardo Biondi Dir. Constitucional Maria Rita Colatti Compiladores Oscarito Vianna NULL Barbara Carlito Estatística Barbara Carlito Sociologia Carlos Maradona Direito Civil Carlos Maradona Dir. Constitucional Sacadura Miranda NULL Maria Lucia Silva Sociologia Maria Lucia Silva Português O quadro mostra as variações sintáticas que expressam a consulta de referência. select a.nome, d.disciplina from ( aluno as a left join inscricao as i on a.matricula = i.matricula ) left join disciplina as d on i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a left outer join inscricao as i on a.matricula = i.matricula left outer join disciplina as d on i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from aluno as a left outer join inscricao as i using (matricula) left outer join disciplina as d using (coddisciplina) select a.nome, d.disciplina from aluno a, inscricao i, disciplina d where a.matricula = i.matricula (+) and i.coddisciplina = d.coddisciplina (+) select a.nome, d.disciplina from aluno as a left outer join inscricao as i on a.matricula = i.matricula left outer join disciplina as d on i.coddisciplina = d.coddisciplina A seguir, as alternativas sintáticas da consulta de referência, porém empregando a operação de right outer join. select a.nome, d.disciplina from ( inscricao as i right join aluno as a on a.matricula = i.matricula ) left join disciplina as d on i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from inscricao as i right outer join aluno as a on a.matricula = i.matricula left outer join disciplina as d on i.coddisciplina = d.coddisciplina select a.nome, d.disciplina from inscricao as i right outer join aluno as a using (matricula) left outer join disciplina as d using (coddisciplina) select a.nome, d.disciplina from aluno a, inscricao i, disciplina d where a.matricula = i.matricula (+) and i.coddisciplina = d.coddisciplina (+) 86 Combinando tabelas: produto ⇒ seleção ⇒ projeção select a.nome, d.disciplina from inscricao as i right outer join aluno as a on a.matricula = i.matricula left outer join disciplina as d on i.coddisciplina = d.coddisciplina FULL OUTER JOIN Por fim, resta a operação de full outer join. Esta operação é extremamente rara porque dificilmente um esquema de bancos de dados corretamente projetado permite e/ou requer sua utilização. Como foram definidas, as tabelas do nosso banco de exemplos não propiciam um exemplo interessante. Será necessário acrescentar um registro à tabela INSCRIÇÃO, de modo a ilustrar a operação. O novo conteúdo da tabela INSCRIÇÃO fica como mostrado a seguir. INSCRIÇÃO Matricula CodDisciplina Nota 1001 112 NULL 1001 317 8,0 1002 210 9,5 1005 316 3,0 1005 117 4,2 1007 112 NULL 1007 114 7,00 1010 317 5,5 0 316 10,00 NULL 210 NULL Note que o registro acrescentado contém o valor nulo para o atributo matrícula. É interessante, neste ponto, observar que o relacionamento entre as tabelas ALUNO e INSCRIÇÃO muda, pois uma determinada inscrição está ligada a zero ou um aluno, conforme mostra o diagrama abaixo. 0:n 0:1 Inscricao Matricula CodDisciplina Nota Aluno Matricula Nome Sexo CodCurso Nascimento X Agora, se fizermos uma operação de full outer join entre as tabelas ALUNO e INSCRIÇÃO, haverá tanto o caso de alunos que não possuem inscrições como de inscrições que não correspondem a aluno algum. Note que relacionamentos desse tipo, em geral, configuram anomalias na base de dados, seja de conteúdo (algum dado digitado erradamente), seja de projeto (relacionamentos 1:N que são opcionais no lado 1). Assim, nossa consulta de referência deve produzir como resultado a lista de todos os alunos e, se for o caso, suas inscrições. Também deve incluir todas as inscrições, mesmo que não possuam um aluno correspondente.nome coddisciplina Ricardo Biondi 112 Ricardo Biondi 317 Maria Rita Colatti 210 Oscarito Vianna NULL Barbara Carlito 316 Barbara Carlito 117 Carlos Maradona 112 Carlos Maradona 114 Sacadura Miranda NULL Maria Lucia Silva 317 Maria Lucia Silva 316 NULL 210 Combinando tabelas com outros fins 87 A raridade deste tipo de operação reflete-se nas implementações SQL: somente o SQL Server aceita uma construção sintática que a formula diretamente. select a.nome, i.coddisciplina from aluno a full outer join inscricao i on a.matricula = i.matricula 5.4 COMBINANDO TABELAS COM OUTROS FINS Nem sempre a combinação de tabelas visa à identificação de registros correspondentes segundo seus elos de ligação lógica. Para ilustrar essa afirmativa, vamos considerar novamente que o conteúdo da tabela ALUNO da base de exemplo tenha sido alterado, como no capítulo 4, e que três registros foram acrescentados ao conteúdo original, introduzindo dois pares de homônimos, Oscarito Vianna e Maria Aparecida da Silva. Matricula Nome Sexo CodCurso Nascimento 1001 Ricardo Biondi M DIR 21/02/80 1002 Maria Rita Colatti F INF 10/11/78 1004 Oscarito Vianna M DIR 14/08/79 1005 Barbara Carlito F JOR 29/10/79 1007 Carlos Maradona M DIR 30/06/77 1008 Sacadura Miranda M INF 12/12/81 1010 Maria Lucia Silva F JOR 10/08/75 1014 Maria Aparecida da Silva F JOR 24/09/77 1015 Oscarito Vianna M INF 12/05/79 1017 Maria Aparecida da Silva F INF 07/04/78 Vamos agora supor que seja preciso construir uma consulta SQL que indique os alunos que têm homônimos na base de dados. Para isso, é preciso comparar cada nome com todos os demais e separar os que são iguais. Entretanto, aqui surge um problema: até aqui, todas as comparações entre registros que fizemos foram entre registros de tabelas diferentes, combinadas pela operação de produto. Como fazer para comparar um registro com os demais da mesma tabela? Pensando em termos de variáveis de registro SQL, podemos imaginar que, se for possível ter duas variáveis distintas sobre a mesma tabela, seria possível fazer as comparações. Recorde que todas as possíveis combinações das instâncias das variáveis são consideradas na construção do resultado. A consulta select a1.matricula, a1.nome, a2.matricula, a2.nome from aluno a1, aluno a2 where a1.nome = a2.nome faz exatamente isso! Vejamos o resultado produzido. a1.matricula a1.nome a2.matricula a2.nome 1001 Ricardo Biondi 1001 Ricardo Biondi 1002 Maria Rita Colatti 1002 Maria Rita Colatti 1015 Oscarito Vianna 1004 Oscarito Vianna 1004 Oscarito Vianna 1004 Oscarito Vianna 1005 Barbara Carlito 1005 Barbara Carlito 1007 Carlos Maradona 1007 Carlos Maradona 1008 Sacadura Miranda 1008 Sacadura Miranda 1010 Maria Lucia Silva 1010 Maria Lucia Silva 1017 Maria Aparecida da Silva 1014 Maria Aparecida da Silva 1014 Maria Aparecida da Silva 1014 Maria Aparecida da Silva 1015 Oscarito Vianna 1015 Oscarito Vianna 1004 Oscarito Vianna 1015 Oscarito Vianna 1017 Maria Aparecida da Silva 1017 Maria Aparecida da Silva 88 Combinando tabelas: produto ⇒ seleção ⇒ projeção 1014 Maria Aparecida da Silva 17 Maria Aparecida da Silva O recurso de utilizar duas variáveis sobre a mesma tabela funciona para podermos comparar cada registro como todos os demais. Acontece que um registro também é comparado com ele mesmo e, sendo os nomes iguais porque trata-se do mesmo registro, uma linha do resultado é construída cada vez que isso acontece. É preciso refinar a consulta, pois sabemos que cada aluno tem uma matrícula diferente e, portanto, os reais homônimos deverão ter matrículas diferentes. A consulta select a1.matricula as matricula1, a1.nome, a2.matricula as matricula2 from aluno a1, aluno a2 where a1.nome = a2.nome and a1.matricula <> a2.matricula produz então matricula1 nome matricula2 1015 Oscarito Vianna 1004 1017 Maria Aparecida da Silva 1014 1004 Oscarito Vianna 1015 1014 Maria Aparecida da Silva 1017 Agora temos somente os que realmente são homônimos no resultado. Porém, ainda há um problema: os nomes aparecem duas vezes. Somente uma linha com cada par de homônimos seria suficiente. Para resolver isso, podemos reformular a consulta como select a1.matricula as matricula1, a1.nome, a2.matricula as matricula2 from aluno a1, aluno a2 where a1.nome = a2.nome and a1.matricula < a2.matricula e teremos o resultado desejado matricula1 nome matricula2 1004 Oscarito Vianna 1015 1014 Maria Aparecida da Silva 1017 Esta última mudança foi sutil. Como a condição anterior aceitava as combinações de registros com matrículas diferentes, os resultados apareciam em dobro, porque quando a variável a1 tinha como instância o registro de matrícula 1004 e variável a2 o registro de matrícula 1014, uma linha foi incluída no resultado. Porém, com as instâncias invertidas, isto é, matrícula 1014 em a1 e matrícula 1004 em a2, outra linha foi incluída. A restrição de que a matrícula da variável a1 seja menor que a matrícula da variável a2 faz com que apenas uma vez essa combinação (matrículas 1004 e 1014) passe na expressão lógica de filtragem. Este exemplo ilustra como a operação de produto pode ser utilizada como um recurso na construção de consultas SQL. Como regra geral, o programador SQL deve lembrar que, sempre que for preciso comparar e/ou combinar registros de tabelas diferentes, ou de uma mesma tabela mais de uma vez, podem-se utilizar variáveis vinculadas a essas tabelas que representem cada um dos seus registros. EXERCÍCIOS 5.1 Escreva uma consulta SQL que produza a lista de professores para os quais há algum aluno com nota ainda não preenchida (valor nulo). O resultado esperado é professor aluno disciplina Exercícios 89 Zenubio Siqueira Carlos Maradona Dir. Constitucional Zenubio Siqueira Ricardo Biondi Dir. Constitucional 5.2 Escreva uma consulta SQL que produza a lista de alunos que estão inscritos na disciplina Português, como mostrado a seguir. matricula nome 1001 Ricardo Biondi 1010 Maria Lucia Silva 5.3 Escreva uma consulta SQL de produza uma lista com os nomes dos professores, em ordem alfabética, e seus respectivos alunos, na mesma ordem, como abaixo. professor aluno Carlos Azambuja Maria Lucia Silva Carlos Azambuja Ricardo Biondi Lenira Rocha Maria Rita Colatti Olivia Straw Carlos Maradona Pedro Amarante Barbara Carlito Silvia Ferreira Barbara Carlito Silvia Ferreira Maria Lucia Silva Zenubio Siqueira Carlos Maradona Zenubio Siqueira Ricardo Biondi 5.4 Suponha que a chave primária da tabela INSCRIÇÃO foi removida e que houve um erro na alimentação do banco de dados que resultou em alguns alunos com inscrições duplicadas, em uma ou mais disciplinas, com notas diferentes, como abaixo. matricula coddisciplina nota 1001 112 NULL 1001 317 8,00 1002 210 9,50 1005 316 3,00 1005 117 4,20 1007 112 NULL 1007 114 7,00 1010 317 5,50 1010 316 10,00 1005 316 5,00 1007 112 8,00 As duas últimas linhas constituem repetições indevidas. Escreva uma consulta SQL que mostre esses alunos, juntamente com o nome da disciplina na qual há repetição de inscrição e a nota atribuída em cada inscrição. O resultado deve aparecer como sugerido a seguir. matricula nome disciplina nota1 nota2 5 Barbara Carlito Sociologia 5,00 3,00 7 Carlos Maradona Dir. Constitucional 8,00 5.5 Uma concessionária de automóveis possui um banco de dados que registra os acessórios que equipam cada veículo sendo vendido. Duas tabelas armazenam esses dados. 90 Combinando tabelas: produto ⇒ seleção ⇒ projeção A tabela Veículo contém o número do chassis de cada veículo juntamente com a soma dos códigos dos acessórios que o equipam, na coluna Acessórios. Por exemplo, o automóvel 200-4753YK está equipado com Direção hidráulica + Rodas de liga leve + CD-player, porque 1+4+8=13. VeículoChassis Acessórios 200-4742YK 31 200-4753YK 13 200-4778YK 11 200-4790YK 15 200-47599K 6 Acessório Código Descrição 1 Direção hidráulica 2 Vidros elétricos 4 Rodas de liga leve 8 CD-Palyer 16 Câmbio automático Para essa base, escreva consultas SQL que produzam: a) a lista de veículos juntamente com seus acessórios, um em cada linha, como chassis descricao 200-4742YK Câmbio automático 200-4742YK CD-Player 200-4742YK Direção hidráulica 200-4742YK Rodas de liga leve 200-4742YK Vidros elétricos 200-4753YK CD-Player 200-4753YK Direção hidráulica 200-4753YK Rodas de liga leve 200-47599K Rodas de liga leve 200-47599K Vidros elétricos 200-4778YK CD-Player 200-4778YK Direção hidráulica 200-4778YK Vidros elétricos 200-4790YK CD-Player 200-4790YK Direção hidráulica 200-4790YK Rodas de liga leve 200-4790YK Vidros elétricos b) os números de chassis dos veículos equipados, pelo menos, com “CD-Player” e “Rodas de liga leve”. O veículo pode ter outros acessórios além desses. chassis 200-4742YK 200-4753YK 200-4790YK c) os números de chassis dos veículos equipados ou com “Rodas de liga leve” ou com “Vidros elétricos”, mas não os dois simultaneamente. O veículo pode ter outros acessórios além desses. chassis 200-4753YK 200-4778YK 5.6 Indique o resultado das consultas abaixo, nas implementações que as permitem. Tente resolver este exercício sem auxílio do computador e explique os resultados. a) select 1 as x from aluno Exercícios 91 b) select 1 as x from aluno, inscricao, disciplina c) select 1 as x from ( aluno a inner join inscricao i on a.matricula = i.matricula ) inner join disciplina d on i.coddisciplina = d.coddisciplina 5.7 Considere duas tabelas R e S, ambas com um atributo a, não necessariamente único. Mostre as condições na quais a consulta select * from r outer join aluno s on r.a = s.a é equivalente a select * from r left join s on r.a = s.a union select * from r right join s on r.a = s.a para quaisquer instâncias de R e S. .
Compartilhar