MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

/*
MySQL Script for SoloLearn Question:
https://www.sololearn.com/Discuss/1834493/?ref=app

Created by: David Carroll
Date: 2019-06-08
*/

CREATE TABLE IF NOT EXISTS UsersA (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL, 
    PRIMARY KEY (id)

)  ENGINE=INNODB;

CREATE TABLE IF NOT EXISTS UsersB (
    id INT AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL, 
    PRIMARY KEY (id)

)  ENGINE=INNODB;

INSERT INTO UsersA (email, password) 
VALUES ('fake01@gmail.com', 'password#hash01');

INSERT INTO UsersA (email, password) 
VALUES ('fake02@gmail.com' , 'password#hash02');

INSERT INTO UsersA (email, password) 
VALUES ('fake03@gmail.com' , 'password#hash03');

INSERT INTO UsersB (email, password) 
VALUES ('fake04@gmail.com', 'password#hash04');

INSERT INTO UsersB (email, password) 
VALUES ('fake05@gmail.com' , 'password#hash05');

INSERT INTO UsersB (email, password) 
VALUES ('fake06@gmail.com' , 'password#hash06');

SET @email = 'fake06@gmail.com';
/* Works as expected. */

SELECT email, password FROM UsersA
WHERE email = @email
UNION ALL
SELECT email, password FROM UsersB
WHERE email = @email;


email
password


fake06@gmail.com
password#hash06


/* Does NOT work the same way. */

SELECT *
FROM UsersA A, UsersB B
WHERE A.email = @email
   OR B.email = @email;


id
email
password
id
email
password


3
fake03@gmail.com
password#hash03
3
fake06@gmail.com
password#hash06

2
fake02@gmail.com
password#hash02
3
fake06@gmail.com
password#hash06

1
fake01@gmail.com
password#hash01
3
fake06@gmail.com
password#hash06

id	email	password	id	email	password
3	fake03@gmail.com	password#hash03	3	fake06@gmail.com	password#hash06
2	fake02@gmail.com	password#hash02	3	fake06@gmail.com	password#hash06
1	fake01@gmail.com	password#hash01	3	fake06@gmail.com	password#hash06