How to create left join using only inner join?

Question

I recently had an interview and was asked how to create a left join using only the inner join. The interviewer told me that some of the programs they use in the company do not allow left-handed connections, so we must use internal connections to execute them. I was stunned by the question, and I was wondering, does anyone know how to do this?

Answer 1

Here is my crack. This basically turns the inner join into a cross, and then NULLing from the values ​​if they don't match the actual desired join condition.

CREATE TABLE #testusers (userid INT)

CREATE TABLE #testusers2 (userID INT)

INSERT INTO #testusers (userid)
VALUES (1)
    , (2)
    , (3)

INSERT INTO #testusers2 (userid)
VALUES (2), (5), (1)


--Expecting to see one result due to straight inner join
SELECT t1.*
    , t2.*
FROM #testusers t1
INNER JOIN #testusers2 t2
    ON t1.userid = t2.userid

--Forcing all results by basically doing a cross apply and then NULLing out non matching values based on the join condition
SELECT distinct t1.*
    , MAX(CASE 
        WHEN t1.userid <> t2.userID
            THEN NULL
        ELSE t2.userid
        END) AS userid
FROM #testusers t1
INNER JOIN #testusers2 t2
    ON t1.userid = t2.userid
        OR 1 = 1
GROUP BY t1.userid

VARCHAR.

CREATE TABLE #testusersnames (username varchar(10))

CREATE TABLE #testusersnames2 (username varchar(10))

INSERT INTO #testusersnames (username)
VALUES ('Tom')
    , ('Jane')
    , ('Dick')

INSERT INTO #testusersnames2 (username)
VALUES ('Tom'), ('Sally'), ('Mary')

SELECT t1.*
    , t2.*
FROM #testusersnames t1
INNER JOIN #testusersnames2 t2
    ON t1.username = t2.username

--Forcing all results by basically doing a cross apply and then NULLing out non matching values based on the join condition
SELECT distinct t1.*
    , MAX(CASE 
        WHEN t1.username <> t2.username
            THEN NULL
        ELSE t2.username
        END) AS username
FROM #testusersnames t1
INNER JOIN #testusersnames2 t2
    ON t1.username = t2.username
        OR 1 = 1
GROUP BY t1.username

Answer 2

. http://sqliteonline.com/

: "" , .

OP UNION, .

CREATE TABLE demo (id int, name varchar(32));

CREATE TABLE demo_details (demo_id int, description varchar(64));

INSERT INTO demo_details
VALUES (1,
        'Woohooooooooo');

SELECT *
FROM demo d
INNER JOIN demo_details dd ON d.id = dd.demo_id
UNION
SELECT d.*,
       NULL,
       NULL
FROM demo d
INNER JOIN demo_details dd ON d.id <> dd.demo_id;

Answer 3

:

SELECT * 
FROM Table1 t1
INNER JOIN (
  SELECT * FROM Table2
  UNION 
  SELECT {NULL for every column in Table2}
) t2
  ON 1=1
WHERE t1.id = t2.id
OR t2.id IS NULL

Answer 4

:

-- dummy data
with a as (
    select 
        1 as id, 
        'A' as name
    union
    select 2, 'B'
),

b as  (
    select 
        1 as id, 
        'C' as value 
)

select *, 
    (select b.value
        from 
    b 
        inner join a a1 
            on a1.id=b.id 
            and a1.id = a.id
    ) as bvalue 
from
    a 

, , !

, , .

Answer 5

, , - select, . , , id , . , , temp , , .

--setup (optional for first time)
DROP TABLE demo;
DROP TABLE demoLeftJoin;
DROP TABLE temp;
--setup
CREATE TABLE demo(id int, lastName varchar(32), hint varchar(120));
CREATE TABLE demoLeftJoin(id int, name varchar(32), hint varchar(120));
--populate demo
INSERT INTO demo VALUES (1, 'Doe', 'nothing');
INSERT INTO demo VALUES (2, 'Doe', 'nothing2');
INSERT INTO demo VALUES (3, 'Deer', 'nothing3');
--populate demoLeftJoin
INSERT INTO demoLeftJoin VALUES (1, 'John', 'nothing');
INSERT INTO demoLeftJoin VALUES (2, 'Jane', 'nothing2');
INSERT INTO demoLeftJoin VALUES (3, 'John', 'nothing3');
INSERT INTO demoLeftJoin VALUES (4, 'Buck', 'nothing4');
INSERT INTO demoLeftJoin VALUES (5, 'Truck', 'nothing5');
--create temp table
CREATE TABLE temp(id int, lastName varchar(32), hint varchar(120));
--make sure it will match an inner join for all values from demoLeftJoin
INSERT INTO temp select id, NULL, NULL from demoLeftJoin;
--populate temp with values you want from demo
UPDATE temp
SET 
    lastName = (SELECT d.lastName FROM demo d WHERE temp.id = d.id), 
    hint = (SELECT d.hint FROM demo d WHERE temp.id = d.id)
WHERE temp.id in (SELECT d.id FROM demo d);
-- perform inner join
SELECT * FROM demoLeftJoin dlj INNER JOIN temp t on dlj.id = t.id;

. , 4- 5- . , . - , , , "hint" "hint2" temp.

Answer 6

+1 @dfundako , , SQL-.

- VIEW,

CREATE VIEW joinedTable AS
  SELECT ... FROM table1 LEFT OUTER JOIN table2 ON ...;

:

SELECT ... FROM joinedTable;

Answer 7

A B " ", B, A

. Sql Union, temp, .

, sql.

. , , , . , . , , , . , . , , ?

.