How to join 2 tables without common fields?

Question

There are 2 tables:

Table 1: first_names

id | first_name 
 1 | Joey 
 7 | Ross
 17| Chandler

Table 2: last_names

id | first_name
 2 | Tribbiani
 7 | Geller 
 25| Bing 

Desired result:

id | full_name
1  | Joey Tribbiani
2  | Ross Geller
3  | Chandler Bing

Task: Write the solution using only the simplest SQL syntax. Using store procedures, declaring variables, ROW_NUMBER(), RANK() functions are forbidden.

I have solution using ROW_NUMBER() function, but no ideas about solving this task using only the simplest SQL syntax.

P.S. I'm only trainee and it's my first question on stackoverflow

Answer 1

Honestly, this is a stupid solution; it's vastly inefficient to ROW_NUMBER, and I wouldn't be surprised if LEAD is "not allowed" as ROW_NUMBER isn't. The fact that you were told to "use the simpliest SQL" means that the SQL you want to use is a subquery/CTE and ROW_NUMBER; that is as simple as this can really go. Anything else add a layer on unneeded complexity and will likely just make the query suffer from performance degradation. This one, for example, means you need to scan both tables twice; where as with ROW_NUMBER it would be once.

CREATE TABLE FirstNames (id int, FirstName varchar(10));
CREATE TABLE LastNames (id int, LastName varchar(10));

INSERT INTO FirstNames
VALUES(1,'Joey'),
      (7,'Ross'),
      (17,'Chandler');

INSERT INTO LastNames
VALUES (2,'Tribbiani'),
       (7,'Geller'),
       (25,'Bing');
GO
WITH CTE AS(
    SELECT FN.id,
           FN.FirstName,
           LN.LastName
    FROM FirstNames FN
        LEFT JOIN LastNames LN ON FN.id = LN.id
    UNION ALL
    SELECT LN.id,
           FN.FirstName,
           LN.LastName
    FROM LastNames LN
        LEFT JOIN FirstNames FN ON LN.id = FN.id
    WHERE FN.id IS NULL),
FullNames AS(
    SELECT C.id,
        C.FirstName,
        ISNULL(C.LastName, LEAD(C.LastName) OVER (ORDER BY id)) AS LastName
    FROM CTE C)
SELECT *
FROM FullNames FN
WHERE FN.FirstName IS NOT NULL
ORDER BY FN.id;


GO

DROP TABLE FirstNames;
DROP TABLE LastNames;

To answer the "Task" given: "Task: Write the solution using only the simplest SQL syntax. Using store procedures, declaring variables, ROW_NUMBER(), RANK() functions are forbidden."

My answer would be the below?

"Why is this a requirement? SQL Server has supported ROW_NUMBER for 14 years, since SQL Server 2005. If you can't use ROW_NUMBER this infers you're using SQL Server 2000. This is actually a big security problem for the company, as 2000 has been out of support for close to a decade. Legislation like GDPR require a company to keep the technology they use secure, and it is very unlikely that this is therefore being met.

If this is the case, the solution if not the find a way around using ROW_NUMBER but to get the company back up to do date. The latest version of SQL Server that you can upgrade to from SQL Server 2000 is 2008; which also runs out of support on July 16 of this year. We'll need to get an instance up and running and get the existing features into this new server ASAP and get QA testing done as soon as possible. This needs to be the highest priority thing. After that we need to repeat the cycle to another version of SQL Server. The latest is 2017, which does support migration from 2008.

Once we've done that, we can then actually make use of ROW_NUMBER in the query; providing the simplest solution and also bringing the company back into a secure environment."

Sometimes requirements need to be challenged. From experience management can make some "stupid" requirements, because they don't understand the technology. When you're in an IT role, sometimes you will need to question those requirements and explain why the requirement isn't actually a good idea. Then, instead, you can aid Management to find the correct solution for the problem. At the end of the day, what they might be trying to fix could be an XY problem; and part of your troubleshooting will be to find out what X really is.

Answer 2

You must create an id to join the tables.
This can be the order number in the table based in ids:

select 
  f.counter id, concat(f.first_name, ' ', l.last_name) full_name
from (
  select t.*, (select count(*) from first_names where id < t.id) + 1 counter
  from first_names t
) f inner join (
 select t.*, (select count(*) from last_names where id < t.id) + 1 counter
  from last_names t
) l
on l.counter = f.counter

See the demo.
Results:

> id | full_name     
> -: | :-------------
>  1 | Joey Tribbiani
>  2 | Ross Geller   
>  3 | Chandler Bing 

Answer 3

Simple join will suffice here

select * from first_names fn
join last_names ln on fn.id = ln.id - 1

But your question is very unclear though. Because join here is based rather on knowledge about Friends series rather than concrete logic...