Select same column from multiple tables only WHERE something = something

Question

I have two tables with very similar structures.

Universidades
nombre | contenido | becas | fotos etc etc etc
Internados
nombre | todo | becas | fotos etc etc etc

I want to write an SQL statement that will select the nombre from both of them and return it as an array only when it matches. From what I have seen UNION SELECT seems to be the way to do this. I added WHERE on the end and I think this is where its going wrong. So far I am receiving the first row of the first table.

What am I typing wrong?

$db = new PDO(DB_DSN, DB_USERNAME, DB_PASSWORD);
$data = $db->prepare("SELECT nombre FROM internados UNION SELECT nombre FROM universidades WHERE nombre = ?");
$data->execute(array($nombre));

apologies, I want to retrive one result from these two tables. Namees in the nombre column are all individual and different in both tables

Answer 1

One issue to point out before we solve the problem is that each query in a UNION is distinct and requires its own WHERE clause. The only clause that applies to the UNION as a whole is ORDER BY. So your query as is needs some tweaking:

SELECT nombre
FROM dbo.internados
WHERE nombre = ? -- the added line
UNION
SELECT nombre
FROM dbo.universidades
WHERE nombre = ?
;

Second, if you want the two tables to both have the same nombre (which is not completely clear but I'm guessing that's right), then that won't work because it simply returns one nombre if the value is found in either table. Probably the best way to solve this is to just do a join:

SELECT I.nombre
FROM
   dbo.internados I
   INNER JOIN dbo.universidades U
      ON I.nombre = U.nombre
WHERE
   I.nombre = ?
   AND U.nombre = ? -- perhaps not needed, but perhaps helpful
;

I am not 100% sure that I understand exactly what you're looking for, so please speak up if I've missed the mark.

You can think about JOIN and UNION this way:

• JOIN: connects rows horizontally
  • Matches them on conditions
  • Creates new columns
  • Doesn't exactly create rows because all data comes from existing rows, but it will duplicate a row from one input when the conditions match multiple rows in the other input. If both inputs have duplicates then it multiplies the count of rows from one input by the count of matching rows from the other.
  • If there is no match condition at all (think CROSS JOIN) then you can get a cartesian product which is each row in one input matched to each row in the other.
  • When using an OUTER join--LEFT, RIGHT, FULL--if rows from the inner input (or either input with FULL) do not match the other, NULLs will be placed into the columns for the other input.
• UNION: stacks rows vertically
  • Generally, creates new rows
  • No conditions are used, there is no real matching
  • UNION by itself (not UNION ALL) will remove duplicate rows, even if one input has no rows

Note that the UNION could be modified to do the job, though this is not ideal:

SELECT nombre
FROM (
   SELECT nombre
   FROM dbo.internados
   WHERE nombre = ?
   UNION ALL
   SELECT nombre
   FROM dbo.universidades
   WHERE nombre = ?
) N
GROUP BY nombre
HAVING Count(*) = 2
;

In this way we ensure there are two values. Note this assumes that there can't be two of the same name in each table. If that's true, more work would be needed to make the UNION method do the job.

Answer 2

most of the time a union can be achieved by doing a join:

SELECT nombre 
  FROM internados 
UNION 
SELECT nombre 
  FROM universidades 
 WHERE nombre = ?

would better be:

SELECT nombre
  FROM internados i
  JOIN universidades u
    ON i.nombre = u.nombre
   AND nombre = ?

which is way simpler to read. (you may also use the JOIN syntax, but I prefer plain old manual joins).

But whether this is a union or a join, always remember that both tables get merged in the result:

nombre | todo | becas | fotos | ... | contenido | becas | ...

so basically, as you put a condition on nombre, you'll either get an empty set, or the name you've given to the query. But hthe way you've written your union, only the second set gets the where condition applied, not the first one. You shall put the condition on both of the queries of the union:

SELECT nombre 
  FROM internados 
 WHERE nombre = ?
UNION 
SELECT nombre 
  FROM universidades 
 WHERE nombre = ?