Sql Server JOINs - SELF JOIN- Parte final

Olá pessoal, para finalizar a nossa série de artigos sobre os tipos de junções disponíveis no sql server 2008, vamos abordar agora a junção SELF JOIN. Está junção nada mais é do que uma junção entre a mesma tabela, para isso utilizamos um alias para diferenciar as tabelas. Para exemplificar a utilização desse operador vamos utilizar uma tabela temporária como mostro abaixo:

CREATE TABLE #EMPREGADO

(

ID INT ,

IDSUPERIOR INT NULL,

NOME VARCHAR(50)

)

INSERT INTO #EMPREGADO VALUES (1,NULL,'BILL')

INSERT INTO #EMPREGADO VALUES (2,1,'MARIA')

INSERT INTO #EMPREGADO VALUES (3,1,'JOSE')

INSERT INTO #EMPREGADO VALUES (4,2,'CARLOS')

INSERT INTO #EMPREGADO VALUES (5,3,'ANA')

SELECT E.ID, ISNULL(S.NOME,'PRESIDENTE') AS SUPERIOR, E.NOME 

FROM #EMPREGADO E LEFT JOIN #EMPREGADO S ON S.ID = E.IDSUPERIOR;

O resultado mostra a lista de empregados e seus superiores:

Veja que no SELF JOIN o operador utilizado é o LEFT JOIN para que o empregado sem superior (presidente) pudesse ser visualizado no resultset. Caso fosse utilizado o INNER JOIN o resultset teria apenas os empregados com chefes. Bom era isso até a próxima!

Sql Server JOINs - CROSS JOIN- Parte 5

Pessoal, neste artigo vamos falar sobre o operador CROSS JOIN. Sua função é criar um produto cartesiano entre 2 ou mais tabelas. Devemos tomar cuidado com esse operador, a depender da quantidade de tabelas e registros envolvidos a consulta poderá ser extremamente custosa. Por exemplo um cross join entre duas tabelas cada uma com 5 registros retornará um resultset com 25 registros, isso porque cada registro da primeira tabela terá uma relação com cada registro da segunda tabela! Vamos ao exemplo:

-- Tabelas temporárias

create table #FUNCIONARIO
(
 IDFUNC INT,
 NOME VARCHAR(40),
 IDDEPAR INT NULL
)
CREATE TABLE #DEPARTAMENTO
(
IDDEPAR INT,
DESCRI VARCHAR(40)
)

--Dados para teste

INSERT INTO #FUNCIONARIO VALUES(1,'MARIA',NULL)
INSERT INTO #FUNCIONARIO VALUES(2,'JOSE',NULL)
INSERT INTO #FUNCIONARIO VALUES(3,'JOAO',1)
INSERT INTO #FUNCIONARIO VALUES(4,'ANA',2)
INSERT INTO #FUNCIONARIO VALUES(5,'SILVA',3)

INSERT INTO #DEPARTAMENTO VALUES (1, 'RH')
INSERT INTO #DEPARTAMENTO VALUES (2, 'ADM')
INSERT INTO #DEPARTAMENTO VALUES (3, 'FIN')
INSERT INTO #DEPARTAMENTO VALUES (4, 'PATR')
INSERT INTO #DEPARTAMENTO VALUES (5, 'SEG')

--Select com CROSS JOIN
SELECT F.IDFUNC, F.NOME, F.IDDEPAR, D.IDDEPAR, D.DESCRI FROM #DEPARTAMENTO D CROSS JOIN #FUNCIONARIO F; 

--Resultado!

Como podem ver, cada funcionário é relacionado com um departamento independente de existir o relacionamento entre chave primária e estrangeira. Até o próximo artigo.

Sql Server JOINs - FULL OUTER JOIN- Parte 4

Olá, continuando a falar sobre os tipos de junções hoje iremos abordar a junção FULL OUTER JOIN.

O operador FULL OUTER JOIN (ou FULL JOIN) tem como função unir e retornar os dados de 2 ou mais tabelas utilizando algum dado que seja comum entre elas. De um modo geral esse dado compreende suas chaves primárias e extrangeiras. Para facilitar a compreensão, nesse artigo não vou utilizar o banco AdventureWorks. Vejamos no script abaixo os resultados que serão retornados.

-- Criar tabelas temporárias

create table #FUNCIONARIO
(
 IDFUNC INT,
 NOME VARCHAR(40),
 IDDEPAR INT NULL
)
CREATE TABLE #DEPARTAMENTO
(
IDDEPAR INT,
DESCRI VARCHAR(40)
)

-- Inserir dados para teste

INSERT INTO #FUNCIONARIO VALUES(1,'MARIA',NULL)
INSERT INTO #FUNCIONARIO VALUES(2,'JOSE',NULL)
INSERT INTO #FUNCIONARIO VALUES(3,'JOAO',1)
INSERT INTO #FUNCIONARIO VALUES(4,'ANA',2)
INSERT INTO #FUNCIONARIO VALUES(5,'SILVA',3)

INSERT INTO #DEPARTAMENTO VALUES (1, 'RH')
INSERT INTO #DEPARTAMENTO VALUES (2, 'ADM')
INSERT INTO #DEPARTAMENTO VALUES (3, 'FIN')
INSERT INTO #DEPARTAMENTO VALUES (4, 'PATR')
INSERT INTO #DEPARTAMENTO VALUES (5, 'SEG')

-- Executar select
SELECT F.IDFUNC, F.NOME, F.IDDEPAR, D.IDDEPAR, D.DESCRI FROM #DEPARTAMENTO D FULL OUTER JOIN #FUNCIONARIO F ON F.IDDEPAR = D.IDDEPAR;
-- Resultado do select

Analisando o resultado podemos chegar a conclusão que o operador FULL JOIN funciona como uma união dos operadores LEFT JOIN e RIGHT JOIN. Execute o select abaixo e compare os resultados.

-- select com union

SELECT F.IDFUNC, F.NOME, F.IDDEPAR, D.IDDEPAR, D.DESCRI FROM #DEPARTAMENTO D LEFT JOIN #FUNCIONARIO F ON F.IDDEPAR = D.IDDEPAR
UNION
SELECT F.IDFUNC, F.NOME, F.IDDEPAR, D.IDDEPAR, D.DESCRI FROM #DEPARTAMENTO D RIGHT JOIN #FUNCIONARIO F ON F.IDDEPAR = D.IDDEPAR

Sql Server JOINs - RIGHT JOIN- Parte 3

Olá pessoal, dando continuidade ao assunto de junções, hoje vamos abordar a junção RIGHT JOIN. A função desse operador é similar a do operador LEFT JOIN, a única mudança fica por conta da tabela que terá todos os seus dados retornados. Neste caso o RIGHT JOIN retornará todos os registros da tabela situada a direita do operador, na tabela da esquerda só serão retornados os dados que tiverem referência da tabela da direita. Vamos exemplificar este caso, utilizando o banco de dados AdventureWorks.

SELECT P.FirstName, P.LastName,  P.BusinessEntityID, E.BusinessEntityID,  E.VacationHours, E.SickLeaveHours
FROM HumanResources.Employee E
RIGHT OUTER JOIN Person.Person P
ON E.BusinessEntityID = P.BusinessEntityID;

Resultado:

Na imagem acima recortei apenas os 10 primeiros registros do resultado (no total são retornados 19972 registros), veja que em todos eles as 3 primeiras colunas, que são da tabela Person, contém algum dado. Como Person está do lado direito do operador RIGHT JOIN todos os registros serão retornados, independente de haver alguma referência na tabela Employee. Vamos ver mais um exemplo utilizando a imagem abaixo:

Se executassemos o seguinte select:

SELECT F.IDFUNC, F.NOME, F.IDDEPAR, D.IDDEPAR, D.DESCRI FROM #FUNCIONARIO F RIGHT JOIN #DEPARTAMENTO D ON F.IDDEPAR = D.IDDEPAR;

Teríamos o seguinte resultado:

Veja que como #DEPARTAMENTO é a tabela a direita do operador RIGHT ela teve todos os seus dados retornados, já os dados da tabela #FUNCIONARIO só são retornados aqueles que possuem departamento.

Sql Server JOINs - LEFT JOIN- Parte 2

Pessoal, dando continuidade aos tipos de junções entre tabelas vamos falar agora sobre as junções do tipo OUTER. Elas podem ser: LEFT, RIGHT e FULL. Nesta publicação falaremos especificamente a respeito da junção LEFT OUTER JOIN. A utilização deste operador permite fazer a junção entre duas tabelas, de modo que a tabela a esquerda do operador LEFT JOIN tenha todos os seus registros retornados (respeitando as condições da cláusula WHERE, se ela existir) independente de existir um registro correspondente na tabela a direita do operador LEFT JOIN. Utilizando o banco de dados AdventureWorks vamos ver um exemplo do funcionamento desse operador com o seguinte select:


SELECT P.FirstName, P.LastName,  P.BusinessEntityID, E.BusinessEntityID,  E.VacationHours, E.SickLeaveHours 
FROM Person.Person P 
LEFT OUTER JOIN HumanResources.Employee E 
ON E.BusinessEntityID = P.BusinessEntityID;

Os 3 primeiros registros retornados do select acima são:

Com base no resultado chegamos a conclusão que no registro 1 temos os campos FirstName, LastName e BusinessEntityID, ambos da tabela Person, e este registro tem uma referência na tabela Employee no campo   BusinessEntityID visto que o valor ambos os campos são iguais a 285, logo as colunas E.BusinessEntityID,  E.VacationHours e E.SickLeaveHours da tabela Employee tem dados. Porem os outros 2 registros não possuem referência na tabela Employee por isso não existe dados nas colunas E.BusinessEntityID,  E.VacationHours e E.SickLeaveHours.

Vimos no exemplo acima que utilizando o operador LEFT JOIN os dados da tabela Person sempre serão retornados, já os da tabela Employee, só serão retornados se existir referência com Person.

Versão do Sql Server utilizada nessa publicação:

Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

Sql Server operador LIKE

Eventualmente precisamos recuperar registros do banco de dados de acordo com um determinado padrão de string. O Sql Server disponibiliza duas formas de efetuar esse filtro, através do operador LIKE ou do predicado CONTAINS. Neste post vamos abordar o funcionamento do operador LIKE.

De uma forma geral utilizamos o operador LIKE na clausula WHERE da seguinte forma:

match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ]

Onde:

• match_expression é uma expressão sql válida que retorna um tipo string (CHAR, VARCHAR, NVARCHAR, NCHAR);
• [NOT] operador opcional utilizado para negar qualquer expressão boleana;
• LIKE operador responsável pelo filtro, retorna TRUE se for encontrado o padrão pattern em match_expression, caso contrario retorna FALSE;
• pattern é o padrão que será utilizado para efetuar o filtro nos registros com base na match_expression, pode ter até 8000 caracteres;
• [ ESCAPE escape_character ] carácter opcional usado como literal para flexibilizar o operador LIKE informando que o carácter coringa deve ser interpretado como um carácter regular não como carácter curinga, veremos alguns exemplos desses caracteres abaixo. 

Para executar os exemplos utilizaremos o Sql Server Management Studio do Microsoft SQL Server 2008 (SP3) - 10.0.5512.0

Num banco de dados qualquer criado por você, abra uma nova consulta com conexão local (arquivo > novo > consulta com conexão local). Vamos criar uma tabela temporária e inserir alguns registros para fazer os testes.

Script da tabela temporária:

CREATE TABLE #CONTATO
(
 ID INT,
 NOME VARCHAR(100),
 EMAIL VARCHAR(100),
 PERCENTUAL VARCHAR(4)
)

Script para inserir os registros:

INSERT INTO #CONTATO VALUES
(1,'Maria de Fatima','mariaf@gmail.com','5%'),
(2,'Antonio Jose','antoniojose@yahoo.com','15%'),
(3,'Fernanda de Carvalho','fc@yahoo.com','70%'),
(4,'Uelinton Josenildo','josenildo.u@gmail.com','35%'),
(5,'Welinton Barbosa','wb@yahoo.com','84%')

Obs: Não feche a sua conexão, está é uma tabela temporária e está em memória, se a conexão for interrompida ela deixará de existir, outra forma de eliminar a tabela é através do comando DROP TABLE #CONTATO)

Vamos aos exemplos:

1- Selecionar todos os registros que no campo nome esteja contido exatamente o termo "Maria de Fatima".

SELECT * FROM #CONTATO WHERE nome like 'Maria de Fatima'

No comando acima estamos dizendo para o banco "eu quero todos os registros que no campo nome contenha exatamente a string "Maria de Fatima". Neste caso teríamos como resultado o registro cujo id é igual a 1.

2- Vamos supor que nossa aplicação executa um procedimento de pesquisa no banco onde a string de pesquisa não contenha espaços em branco, todo espaço em branco é substituído por underline, por exemplo "Antonio_Jose". Então para pesquisar todos os registros que no campo nome esteja contido exatamente o termo "Antonio_Jose" precisaremos fazer uma operação na coluna nome ficando da seguinte forma:

SELECT * FROM #CONTATO where REPLACE(nome,' ','_') LIKE 'Antonio_Jose'

Neste caso o operador LIKE está validando o resultado da expressão (REPLACE(nome,' ','_')) que contem a coluna nome, em vez de validar o termo diretamente na coluna como no exemplo 1. Neste caso teríamos como resultado o registro cujo id é igual a 2.

3- Agora vamos começar a flexibilizar o operador LIKE. Queremos selecionar todos os registros cuja a coluna nome contenha, em qualquer lugar, a string "Jose". Neste caso devemos utilizar o carácter curinga %.

SELECT * FROM #CONTATO where nome LIKE '%Jose%'

No comando acima estamos dizendo, "eu quero todos os registros que contenha o termo 'Jose' em qualquer parte da string presente na coluna nome". Desta forma seriam retornados os registros 2 (Antonio Jose) e 4 (Uelinton Josenildo). Se quiséssemos apenas os registros cujo o campo nome começassem com a string 'Jose' o carácter curinga só seria colocado a frente do termo, formando o pattern 'Jose%'. Como não existe nenhum nome que comece com Jose, nenhum registro será retornado.

4- E se o campo a ser pesquisado contivesse um carácter curinga  Neste caso precisamos de um artifício que faça o Sql Server interpretar o carácter curinga como um carácter literal. Para resolver o problema utilizamos a palavra reservada ESCAPE e um carácter auxiliar como no exemplo abaixo:

SELECT * FROM #CONTATO WHERE PERCENTUAL LIKE '%@%%' ESCAPE '@'

O select acima retornará todos os registros porque todos os dados da coluna percentual terminam com o sinal "%" que neste caso é interpretado como um carácter literal. Outra forma de obter o mesmo resultado seria colocando o carácter curinga entre colchetes. Assim:

SELECT * FROM #CONTATO WHERE PERCENTUAL LIKE '%[%]%'

5- Podemos pesquisar um único carácter também. Para obter esse recurso utilizamos o carácter underline. O select abaixo pode ser traduzido da seguinte forma: Pegue todos os contatos cujo nome comece com qualquer letra e termine com 'elinton'.

SELECT * FROM #CONTATO WHERE NOME LIKE '_elinton'

O select acima não retornará nenhum registro porque não existe nenhum contato onde o nome tenha apenas uma palavra. No exemplo 6 poderemos resolver esse problema.

6- Continuando com o mesmo problema do exemplo 5, faremos uma pequena modificação para poder obter todos os nomes que comecem com qualquer letra seguido da string 'elinton', porem desconsideraremos a string que vier depois, se houver. Neste caso devemos utilizar um segundo carácter curinga  Sim! Podemos utilizar mais de um carácter curinga. Vejamos como fica:

SELECT * FROM #CONTATO WHERE NOME LIKE '_elinton%'

Como resultado teremos os registros de id 4 e 5. Porque estamos dizendo:"eu não sei qual é a primeira letra mas depois dela vem a string 'elinton' e depois pode vir qualquer coisa".

7- No exemplo 6 resolvemos o problema da pesquisa, mas deixamos muito flexível, visto que o servidor terá que testar todos os carácteres possíveis. Podemos limitar essa pesquisa da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[UW]elinton%'

Veja que agora estamos dizendo, "eu não sei qual é a primeira letra, mas sei que pode ser U ou W seguido de 'elinton' e o resto dos carácteres pode ser qualquer coisa.". Dessa forma limitamos o primeiro curinga a duas letras, mas poderia ser também um intervalo da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[M-W]elinton%'

Assim o Sql Server tentará encontrar um nome cuja a primeira letra seja alguma de M até W.

8- E por fim podemos usar o carácter ^ (acento circunflexo) para excluir um carácter, conjunto ou intervalo. Vejamos abaixo como fica:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^W]elinton%' 

No exemplo acima estamos dizendo "o primeiro carácter pode ser qualquer um menos o W". Neste caso somente o registro cujo id é 4 será retornado. Abaixo, vamos fazer a exclusão de um grupo de carácteres:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^ABCD]elinton%'

Acima podemos ver como podemos dizer ao Sql Server que não queremos determinados carácteres. No exemplo informamos que os contatos podem começar com qualquer letra menos A,B,C,D, seguido da string 'elinton'. O exemplo acima poderia ser escrito também como um intervalo da seguinte forma:

SELECT * FROM #CONTATO WHERE NOME LIKE '[^A-D]elinton%'

Bom pessoal, espero ter conseguido exemplificar um pouco do funcionamento do operador LIKE no Sql Server. Antes de terminar gostaria de salientar algumas observações importantes:

1. Verifique a collation do seu servidor/banco/tabela/coluna, a depender da configuração o resultado pode não ser o esperado;
2. Atente para os tipos de dados da match_expression e pattern, CHAR e VARCHAR armazenam os dados de forma diferente;
3. Quandos os argumentos (match_expression e pattern) são do tipo ASCII tudo será executado como ASCII, caso um dos argumentos sejam Unicode, todos serão convertidos para Unicode.

Sql Server JOINs - INNER JOIN- Parte 1

Em banco de dados normalizados, as informações necessárias para um único conjunto de dados podem estar localizadas em mais de uma tabela. Para suprir essa necessidade, utilizamos o operador JOIN.
Existem vários tipos de operadores JOINs, cada um com seu propósito dentro desse contexto de junção de tabelas. Os operadores são:

INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER, e CROSS.

Neste post vamos falar especificamente do operador INNER. Quando desejamos fazer junções entre tabelas, precisamos definir as tabelas que serão relacionadas, o tipo de operador JOIN que será utilizado e o padrão que será utilizado para retonar os registros das tabelas. No exemplo abaixo (executado no banco Adventure Works) as tabelas são: "HumanResources.Employee" e "Person.Person", o operador é "INNER JOIN" e a condição ou padrão é "HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID"

SELECT FirstName,LastName,JobTitle,VacationHours,SickLeaveHours
FROM HumanResources.Employee 
INNER JOIN Person.Person
ON HumanResources.Employee.BusinessEntityID = Person.Person.BusinessEntityID;

Quando não especificamos o tipo de JOIN (FROM HumanResources.Employee
JOIN Person.Person) o Sql Server adota o INNER JOIN como padrão.
Na clausula da condição estamos especificando o nome completo (schema.tabela.coluna) da coluna para evitar ambiguidade, visto que possuem o mesmo nome e estão em ambas as tabelas.

Mas qual a definição do INNER JOIN? Basicamente ele retorna os registros das tabelas especificadas que satisfação a condição. Só serão retornados os registros da tabela Employee que tenha o valor do seu campo BusinessEntityID referênciado na tabela Person no campo BusinessEntityID. Execute a query acima e veja o resultado. O registro de Employee que não tiver nenhuma referência em Person não será retornado e vice-versa.

No exemplo acima o SELECT possui apenas 2 tabelas, porem o INNER JOIN pode ser utilizado N vezes seguindo a regra descrita acima. Na condição do INNER JOIN utilizamos os campos que representam a chave primária e estrangeira das tabelas de modo a garantir a integridade referencial dos dados. Alem disso a condição especificada possui apenas uma regra mas pode existir N regras também, vejamos o exemplo abaixo:


SELECT FirstName,LastName,JobTitle,VacationHours,SickLeaveHours
FROM HumanResources.Employee E
INNER JOIN Person.Person P ON E.BusinessEntityID = P.BusinessEntityID and VacationHours > 30
INNER JOIN HumanResources.EmployeeDepartmentHistory EH ON EH.BusinessEntityID = E.BusinessEntityID;


Versão do Sql Server utilizada:
Microsoft SQL Server 2008 (SP3) - 10.0.5512.0