How to unite results of queries without mixing them?

Asked

Viewed 65 times

1

How can I join 2 query results without mixing them?

For example: The first query results in names starting with Victor, the second results in names containing Victor.

Result of first consultation:

Vitor A...
Vitor B...
Vitor C...
Vitor D...

Result of the second consultation:

Antonio Vitor...
João Vitor...
Matheus Costa Vitor
Vitor A...
Vitor B...
Vitor C...
Vitor D...
William Vitor

Uniting them results in:

Antonio Vitor...
João Vitor...
Matheus Costa Vitor
Vitor A...
Vitor B...
Vitor C...
Vitor D...
William Vitor

My goal is to result in:

Vitor A...
Vitor B...
Vitor C...
Vitor D...
Antonio Vitor...
João Vitor...
Matheus Costa Vitor
William Vitor

I am using the following query:

SELECT Nome
FROM Pessoa
WHERE Nome LIKE 'Vitor%'

UNION  

SELECT Nome
FROM Pessoa
WHERE Nome LIKE '%Vitor%'
sql sql-server sql-server-2014

  • can insert into a temporary/in-memory table to return in the same order they were inserted

  • Yeah, if it doesn’t make it too slow.

1 answer

3


The first thing I’d do is make sure the set didn’t overlap, so we could work with UNION ALL.

How to do this? Simple: in the second selection, add the filter that you can’t start with Vitor:

SELECT nome
FROM pessoa
WHERE nome like '%Vitor%' and nome not like 'Vitor%'

Uniting everything with UNION ALL:

SELECT nome
FROM pessoa
WHERE nome like 'Vitor%'
UNION ALL
SELECT nome
FROM pessoa
WHERE nome like '%Vitor%' and nome not like 'Vitor%'

Okay, now we need to conserve the relative order of the projected elements. Among other alternatives, I like to put a classifier for each query and sort by that classifier:

SELECT nome, 1 as classificador
FROM pessoa
WHERE nome like 'Vitor%'
UNION ALL
SELECT nome, 2 as classificador
FROM pessoa
WHERE nome like '%Vitor%' and nome not like 'Vitor%'
ORDER BY classificador

If you need to guarantee, among each collection of elements, the alphabetical order, just put other ordering conditions after the classifier I created:

SELECT nome, 1 as classificador
FROM pessoa
WHERE nome like 'Vitor%'
UNION ALL
SELECT nome, 2 as classificador
FROM pessoa
WHERE nome like '%Vitor%' and nome not like 'Vitor%'
ORDER BY classificador, nome

But my projection can only contain nome, as it does?

You can put in a sub-volume what I wrote or in a CTE (which is a more elegant sub-unit). Example with CTE:

WITH q AS (
  SELECT nome, 1 as classificador
  FROM pessoa
  WHERE nome like 'Vitor%'
  UNION ALL
  SELECT nome, 2 as classificador
  FROM pessoa
  WHERE nome like '%Vitor%' and nome not like 'Vitor%'
  ORDER BY classificador, nome
)
SELECT nome FROM q

  • First, thank you, it worked. Second, there’s a difference between UNION and UNION ALL in this case? I tested both and returned the same results.

  • Another question, between the and and not like 'Vitor%' wouldn’t have to put nome, thus remaining and nome not like 'Vitor%'?

  • @Foci even more than UNION ALL is a simpler operation and works less. As I have the guarantee that the result of the operation will be exactly the same (see the differential of tuples), I prefer to use UNION ALL. There is also an algebraic question, I prefer to work with bags than with sets, and UNION is joint operation. I have answered some of this here: https://answall.com/a/258431/64969

  • @Focus on this second comment: typo error, thanks for noticing

  • @Foci should have a more optimized way of putting this nome LIKE '%vitor%' AND nome NOT LIKE 'vitor%, but I’m not a great connoisseur of other metacharacters of the LIKE

Browser other questions tagged sql sql-server sql-server-2014

You are not signed in. Login or sign up in order to post.