SQL: Two select statements in one query

Question

I need to select the information from two SQL tables within a single query, the information is irrelevant though, so no potential joints exist.

An example could be the following setup.

tblMadrid:

   id | name    | games | goals
    1 | ronaldo | 100   | 100
    2 | benzema | 50    | 25
    3 | bale    | 75    | 50
    4 | kroos   | 80    | 10

tblBarcelona:

   id | name    | games | goals
    1 | neymar  | 60    | 25
    2 | messi   | 150   | 200
    3 | suarez  | 80    | 80
    4 | iniesta | 40    | 5

I am in need of a query that gives me the following:

name    | games | goals
messi   | 150   | 200
ronaldo | 100   | 100

I tried to follow this logic, but the below code did not work:

USE Liga_BBVA

SELECT (SELECT name,
               games,
               goals
        FROM   tblMadrid
        WHERE  name = 'ronaldo') AS table_a,
       (SELECT name,
               games,
               goals
        FROM   tblBarcelona
        WHERE  name = 'messi')   AS table_b
ORDER  BY goals 

Info: The football stuff is only a simplifying example. In reality, it's not possible to put both tables into a single table and have a new "team" column. The two tables have totally different structures, but I want something that matches the characteristics of this example

Answer 1

You can do something like below:

(SELECT
    name, games, goals
    FROM tblMadrid WHERE name = 'ronaldo')
 UNION
 (SELECT
    name, games, goals
    FROM tblBarcelona WHERE name = 'messi')
ORDER BY goals;

You can check an example from here.

Are you interested to know SQL in depth? Apply for and join this SQL Course and gain deep knowledge.

You can check out the below SQL Tutorial video for better understanding.