-- Run this script using MariaDB's command-line client like so:
--
--	mysql -B -s < this_file.sql


-- Replace "TestDB" with whatever DB you'd like to use.
-- You can also skip the DB create/drop and use CREATE TEMPORARY TABLE.
DROP DATABASE IF EXISTS TestDB;
CREATE DATABASE TestDB;
USE TestDB;



-- Here are the two tables we will join:
CREATE TABLE Table_A (F1 VARCHAR(100)
) ENGINE=InnoDB;

CREATE TABLE Table_B (F1 VARCHAR(100), 
		      F2 VARCHAR(100),
		      INDEX (F1),
		      INDEX (F2)
) ENGINE=InnoDB;


-- Note: None of the 'Extra' records should be SELECTed in our test output.
-- Therefore, they SHOULD be irrelevant.  But they are not!
-- Experiment by removing a few of them; it changes the output.
-- Experiment by rearranging the order of them; it also changes the output.
INSERT INTO Table_A (F1) VALUES
       ('Join on this'),
       ('Not Used in Join');
INSERT INTO Table_B (F1, F2) VALUES
       ('Join on this', 'Extra'),
       ('Join on this', 'Extra'),
       ('Join on this', 'Extra'),
       ('Join on this', 'Extra'),
       ('Join on this', 'Extra'),
       ('Join on this', 'Extra'),
       ('Join on this', 'Extra'),
       ('Join on this', 'One Of These'),
       ('Join on this', 'Two Of These'),
       ('Join on this', 'Two Of These');


-- Write some test results for the user running this script:
SELECT 'We should expect one record with "One Of These":';
SELECT F2 FROM Table_A JOIN Table_B USING (F1)
       WHERE F2 IN ('One Of These');
SELECT 'Records returned:',
       (SELECT COUNT(*) FROM Table_A JOIN Table_B USING (F1)
        WHERE F2 IN ('One Of These'));

SELECT '';

SELECT 'We should expect two records with "Two Of These":';
SELECT F2 FROM Table_A JOIN Table_B USING (F1)
       WHERE F2 IN ('Two Of These');
SELECT 'Records returned:',
       (SELECT COUNT(*) FROM Table_A JOIN Table_B USING (F1)
        WHERE F2 IN ('Two Of These'));

SELECT '';

SELECT 'We should get one "One Of These" and two "Two Of These":';
SELECT F2 FROM Table_A JOIN Table_B USING (F1)
       WHERE F2 IN ('Two Of These', 'One Of These');
SELECT 'Records returned:',
       (SELECT COUNT(*) FROM Table_A JOIN Table_B USING (F1)
        WHERE F2 IN ('Two Of These', 'One Of These'));

-- This is just to remove our temporary test database.
-- If you are using a real database, just remove/comment this next line:
DROP DATABASE TestDB;


-- Local Variables:
-- mode: sql
-- sql-product: mysql
-- coding: utf-8
-- End:
