UNION statement not working, forced to use JOIN (Performance dominance by either?)
<p>I have a two identical tables from different servers. as a short fix (to help handle a longer fix since we are dealing with VERY legacy Infrastructure) to identifying duplicate PK's, we've decided to have the 1st phase be gathering a list of records whose Primary Key exists in the other database whose table is identical. Even though they would have the same PK's, they would still have different data in the rest of the tuple (or record) such as first name, last name, etc..</p>

<p>I am trying to use this UNION statement since they are both identical tables, but keep getting errors as to "Unknown columns in the 'Where' clause"</p>

<pre><code>SELECT T.`hospitalno` AS HospitalnoMain, T.`dateencoded` AS DateEncodedMain,
    T.`firstname` AS FirstNameMain, T.`lastname` AS LastNameMain
FROM registration.`patmaster` T
UNION
SELECT P.`hospitalno` AS HospitalnoAux, P.`dateencoded` AS DateEncodedAux,
    P.`firstname` AS FirstNameAux, P.`lastname` AS LastNameAux
FROM registration.`patmaster` P
WHERE T.`LastNameMain` &lt;&gt; P.`LastNameAux` AND T.`FirstNameMain` &lt;&gt; P.`FirstNameAux`
LIMIT 100
</code></pre>

<p>So I've settled for this statement that is pulling the correct data (at least through all the adjustments to records to test the query, it is correct).</p>

<pre><code>SELECT * FROM 
(
    SELECT T.`hospitalno` AS HospitalnoMain, T.`dateencoded` AS DateEncodedMain,
        T.`firstname` AS FirstNameMain, T.`lastname` AS LastNameMain
    FROM registration.`patmaster` t
) AS G
LEFT OUTER JOIN
(
    SELECT P.`hospitalno` AS HospitalnoAux, P.`dateencoded` AS DateEncodedAux,
        P.`firstname` AS FirstNameAux, P.`lastname` AS LastNameAux
    FROM registration.`patmaster` p
) H
    ON G.HospitalnoMain = H.HospitalnoAux
WHERE G.`LastNameMain` &lt;&gt; H.`LastNameAux` AND G.`FirstNameMain` &lt;&gt; H.`FirstNameAux`
LIMIT 100
</code></pre>

<p>--
please excuse the manual entry format.</p>

<p>My question is, which is better performance-wise since it will be pulling large amounts of data through BOTH tables. and if it is UNION, what is wrong with my UNION statement since I've tried variations to the SELECT statement that keep giving me the same "unknown column..." error. Thank you in advance</p>

<p>EDIT
also, if anyone knows how to use SQL in "Code" i would appreciate it. It does not seem to work here as</p>