It is necessary to find one record in two tables with different structure but with the same field name, I try this:

$sql = 'SELECT * FROM (SELECT * FROM category_tree DESC)'. 'UNION'. '(SELECT * FROM pages) WHERE url = :url';

In each table there is a field 'url'

  • one
    It is impossible to combine tables with different structure. How do you see the result? - msi
  • If the URL is used as a unique key, then why hasn’t anyone offered the usual LEFT JOIN? - uorypm
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

3 answers 3

The WHERE in SQL applies to a specific SELECT ; for the whole UNION you cannot specify WHERE .

In addition, you indicate that the table structure is different, and UNION requires that all subqueries return the same number of columns with the same data types. Therefore, it is inadmissible to use * in the select list, it is necessary to enumerate explicitly those columns that each of the subqueries must return and their number / types must match.

So your request should look something like this:

  SELECT col1, col2, col3 FROM category_tree WHERE url = :url UNION SELECT col1, col2, col3 FROM pages WHERE url = :url

In principle, you can of course UNION from two tables without the conditions of the sample to enclose in brackets and make an external SELECT for which it will be in FROM , but I do not cite this option, since it is not recommended to do so categorically since the query optimizer in this case will not be able to correctly apply indexes, even if they exist, will apply the connection of all tables at a time and the query execution time and the server load will be very large.

    If you also have other identical columns in the tables, and you want to distinguish them in the answer:

     SELECT category_tree.*,pages.* FROM category_tree, pages WHERE category_tree.url = pages.url
    • Thanks, I didn’t even think that you would answer so quickly, but decided not to make such a long request, but split it into 2 MVC methods - JJXX
     SELECT * FROM category_tree, pages WHERE category_tree.url = pages.url
    • Thank you all for your responsiveness - JJXX