Correct Way to Select from Two Tables in SQL Server with No Common Field to Join On

Correct way to select from two tables in SQL Server with no common field to join on

You can (should) use CROSS JOIN. Following query will be equivalent to yours:

SELECT 
   table1.columnA
 , table2.columnA
FROM table1 
CROSS JOIN table2
WHERE table1.columnA = 'Some value'

or you can even use INNER JOIN with some always true conditon:

FROM table1 
INNER JOIN table2 ON 1=1

How join two tables using SQL without a common column

You can use ROW_NUMBER window function to create a calculated field that can be used to join the two tables together:

SELECT t1.Column1, t1.Column2, t2.Column3, t2.Column4
FROM (
   SELECT Column1, Column2,
          ROW_NUMBER() OVER (ORDER BY Column1) AS rn
   FROM Table1) AS t1
FULL OUTER JOIN  (
   SELECT Column3, Column4,
          ROW_NUMBER() OVER (ORDER BY Column3) AS rn
   FROM Table2) AS t2
ON t1.rn = t2.rn

A FULL OUTER JOIN is necessary in case either Table1 or Table2 has more rows.

Combine two tables that have no common fields

There are a number of ways to do this, depending on what you really want. With no common columns, you need to decide whether you want to introduce a common column or get the product.

Let's say you have the two tables:

parts:              custs:
+----+----------+   +-----+------+
| id | desc     |   |  id | name |
+----+----------+   +-----+------+
|  1 | Sprocket |   | 100 | Bob  |
|  2 | Flange   |   | 101 | Paul |
+----+----------+   +-----+------+

Forget the actual columns since you'd most likely have a customer/order/part relationship in this case; I've just used those columns to illustrate the ways to do it.

A cartesian product will match every row in the first table with every row in the second:

> select * from parts, custs;
      id desc     id  name
      -- ----     --- ----
      1  Sprocket 101 Bob
      1  Sprocket 102 Paul
      2  Flange   101 Bob
      2  Flange   102 Paul

That's probably not what you want since 1000 parts and 100 customers would result in 100,000 rows with lots of duplicated information.

Alternatively, you can use a union to just output the data, though not side-by-side (you'll need to make sure column types are compatible between the two selects, either by making the table columns compatible or coercing them in the select):

> select id as pid, desc, null as cid, null as name from parts
  union
  select null as pid, null as desc, id as cid, name from custs;
    pid desc     cid name
    --- ----     --- ----
                 101 Bob 
                 102 Paul
    1   Sprocket
    2   Flange

In some databases, you can use a rowid/rownum column or pseudo-column to match records side-by-side, such as:

id desc     id  name
-- ----     --- ----
1  Sprocket 101 Bob
2  Flange   101 Bob

The code would be something like:

select a.id, a.desc, b.id, b.name
from parts a, custs b
where a.rownum = b.rownum;

It's still like a cartesian product but the where clause limits how the rows are combined to form the results (so not a cartesian product at all, really).

I haven't tested that SQL for this since it's one of the limitations of my DBMS of choice, and rightly so, I don't believe it's ever needed in a properly thought-out schema. Since SQL doesn't guarantee the order in which it produces data, the matching can change every time you do the query unless you have a specific relationship or order by clause.

I think the ideal thing to do would be to add a column to both tables specifying what the relationship is. If there's no real relationship, then you probably have no business in trying to put them side-by-side with SQL.

If you just want them displayed side-by-side in a report or on a web page (two examples), the right tool to do that is whatever generates your report or web page, coupled with two independent SQL queries to get the two unrelated tables. For example, a two-column grid in BIRT (or Crystal or Jasper) each with a separate data table, or a HTML two column table (or CSS) each with a separate data table.

Joining tables without a common column in sql server

Using FULL OUTER JOIN, you can get the expected result.

Since there are no common fields, no records from Table1 should match with Table2 and vice versa. So perhaps ON 0 = 1 as the join condition also will work as expected. Thanks Bart Hofland

So the query below also will work:

SELECT T1.Id, T2.[Name]
FROM Table1 T1
FULL OUTER JOIN Table2 T2 ON 0 = 1;

or

SELECT T1.Id, T2.[Name]
FROM Table1 T1
FULL OUTER JOIN Table2 T2 ON T2.[Name] = CAST(T1.Id AS VARCHAR(2));

Demo with the sample data:

DECLARE @Table1 TABLE (Id INT);

INSERT INTO @Table1 (Id) VALUES
(1),                           
(2),                           
(3),                           
(4),                           
(5);  

DECLARE @Table2 TABLE ([Name] VARCHAR(1));

INSERT INTO @Table2 ([Name]) VALUES
('Z'),
('Y'),
('X'),
('W'),
('V');

SELECT T1.Id, T2.[Name]
FROM @Table1 T1
FULL OUTER JOIN @Table2 T2 ON 0 = 1;

Output:

Id      Name
-----------------
1       NULL
2       NULL
3       NULL
4       NULL
5       NULL
NULL    Z
NULL    Y
NULL    X
NULL    W
NULL    V

Select data from multiple table without join sql

You could create an on-the-fly join column via ROW_NUMBER, and then join on that:

WITH cte1 AS (
    SELECT ColumnA, ROW_NUMBER() OVER (ORDER BY ColumnA) rn
    FROM TableA
),
cte2 AS (
    SELECT ColumnD, ROW_NUMBER() OVER (ORDER BY ColumnD) rn
    FROM TableB
)

SELECT t1.ColumnA, t2.ColumnD
FROM cte t1
INNER JOIN cte t2
    ON t1.rn = t2.rn;

Of course, this would just pair the 10K records using the arbitrary orderings in the A and D columns. If you have some specific logic for how these two columns should be paired up, then let us know. The bottom line is that you can't easily get away from the concepts of join or union to bring these two columns together.

Data extraction from two tables with no common field

You could do the following:

select ap.SESSION_NO,
       ap.SESSION_BEG,
       us.SEGMENT_NAME,
       us.BYTES/1024/1024 AS "SIZE in MB"
  from APP_SESSION ap
  INNER JOIN USER_SEGMENTS us
    ON us.SEGMENT_TYPE = 'TABLE' AND
       us.SEGMENT_NAME LIKE '%' || ap.SESSION_NO || '%'
  where ap.SESSION_BEG < to_date('2020-11-01 00:00:00', 'YYYY-MM-DD HH24:MI:SS')
  order by ap.SESSION_BEG,
           "SIZE in MB"

Joining tables in query with no common fields

There doesn't appear to be a direct relationship between book and student

• There is a relationship between book and copy (isbn)
• There is a relationship between copy and loan (code)
• There is a relationship between loan and student (no)

Therefore we do have an indirect relationship between book and student

Try this on for size:

SELECT book.isbn
     , book.title
     , book.author
     , copy.code
     , copy.duration
     , loan.taken
     , loan.due
     , loan.return
     , student.no
     , student.name
     , student.school
     , student.embargo
FROM   book
 INNER
  JOIN copy
    ON copy.isbn = book.isbn
 INNER
  JOIN loan
    ON loan.code = copy.code
 INNER
  JOIN student
    ON student.no = loan.no

P.S. do you control this data structure? If so there are some changes that could be recommended.

How to join two SQL Server tables which have no common column

So , your cParaCD column from table1 have first 3 characters equal to your column cFtyCD from Table2. If this criteria (which is evident from the sample data shared by you for both tables) remains same for all your rows in first and second table then below query can be used.

I am essentially taking out first 3 characters from cParaCD and then equating them to value in column cFtyCD.

Select
nParaID ,  cParaNo    ,cParaYear    ,cParaCD    ,cFtyCD    ,cInvNo
from Table1 a 
join Table2 b on substring(a.cParaCD,1,len(b.cFtyCD)) = b.cFtyCD

---

Related Topics