You are trying to join to tables without a common key. This can be achieved by calculating a ROW_NUMBER() for each row, and joining the two DEs on rowNumber. This isn't random yet but will keep the order of both DEs.

So: If you only need to perform this once, you could randomize one of your DEs by creating a Data Extension of type "random", "splitting" it into resulting DE with 100% of records, which will physically randomize the order of rows.

then you can just run this.

SELECT 
sub1.fieldInDE1
,sub2.fieldInDE2 
FROM (
   SELECT deOne.fieldInDE1,
          ROW_NUMBER() OVER (ORDER BY deOne.fieldInDE1) AS rn
   FROM myTable1 deOne
   ) AS sub1
INNER JOIN  (
   SELECT fieldInDE2,
          ROW_NUMBER() OVER (ORDER BY fieldInDE2) AS rn
   FROM myTable2 deTwo
   ) AS sub2
ON sub1.rn = sub2.rn

If you need to do this regularly or want to have it as pure SQL, then combine the above with an ORDER BY NEWID() in one of the tables, which will randomize one of the DEs on-the-fly:

SELECT 
sub1.fieldInDE1
,sub2.fieldInDE2 
FROM (
   SELECT deOne.fieldInDE1,
          ROW_NUMBER() OVER (ORDER BY deOne.fieldInDE1) AS rn
   FROM myTable1 deOne
   ) AS sub1
INNER JOIN  (
   SELECT deTwo.fieldInDE2,
          ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
   FROM myTable2 deTwo
   ) AS sub2
ON sub1.rn = sub2.rn

You are trying to join to tables without a common key. This can be achieved by calculating a ROW_NUMBER() for each row, and joining the two DEs on rowNumber. This isn't random yet but will keep the order of both DEs.

So: If you only need to perform this once, you could randomize one of your DEs by creating a Data Extension of type "random", "splitting" it into resulting DE with 100% of records, which will physically randomize the order of rows.

then you can just run this.

SELECT 
sub1.fieldInDE1
,sub2.fieldInDE2 
FROM (
   SELECT deOne.fieldInDE1,
          ROW_NUMBER() OVER (ORDER BY deOne.fieldInDE1) AS rn
   FROM myTable1 deOne
   ) AS sub1
INNER JOIN  (
   SELECT fieldInDE2,
          ROW_NUMBER() OVER (ORDER BY fieldInDE2) AS rn
   FROM myTable2 deTwo
   ) AS sub2
ON sub1.rn = sub2.rn

If you need to do this regularly or want to have it as pure SQL, then combine the above with an ORDER BY NEWID() in one of the tables, which will randomize one of the DEs on-the-fly:

SELECT 
sub1.fieldInDE1
,sub2.fieldInDE2 
FROM (
   SELECT deOne.fieldInDE1,
          ROW_NUMBER() OVER (ORDER BY deOne.fieldInDE1) AS rn
   FROM myTable1 deOne
   ) AS sub1
INNER JOIN  (
   SELECT deTwo.fieldInDE2,
          ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
   FROM myTable2 deTwo
   ) AS sub2
ON sub1.rn = sub2.rn

Edit: as per your comment: If your tables arent equal in length, then try:

Cross apply the long with the short table. This will give you what you want, but in multiples of the number of rows of the short table. In your case, the expected result, but 3 times.

Hence, deduplicate the result using ROW_NUMBER() / OVER / PARTITION BY, returning only values where Row_number = 1. In this process, we also randomize one table using ORDER BY NEWID()

SELECT
a.fieldNameLongTable 
,a.fieldNameShortTable 
FROM 
    (SELECT
        t1.fieldNameLongTable
        ,t2.fieldNameShortTable
        ,ROW_NUMBER() OVER(Partition by t1.fieldNameLongTable ORDER BY NEWID()) AS Row_Number
        FROM longTable t1
        
        CROSS APPLY
        (SELECT fieldNameShortTable
        FROM shortTable
        ) t2
    ) a
WHERE a.Row_number = 1