table1
no name
1 aaa
2 bbb
3 ccc
table2
date
1/1/19
2/2/19
result will be
no name date
1 aaa 1/1/19
2 bbb 1/1/19
3 ccc 1/1/19
1 aaa 2/2/19
2 bbb 2/2/19
3 ccc 2/2/19
Asked
Active
Viewed 61 times
-2
Amira Bedhiafi
- 8,088
- 6
- 24
- 60
-
1What's the question here? – Ilyes Nov 06 '19 at 11:27
-
5use `CROSS JOIN` – Squirrel Nov 06 '19 at 11:32
-
You need to show some code for what you have attempted so that we can see where you are going wrong. – bilpor Nov 06 '19 at 11:34
-
Possible duplicate of [CROSS JOIN vs INNER JOIN in SQL Server 2008](https://stackoverflow.com/questions/17759687/cross-join-vs-inner-join-in-sql-server-2008) – Amira Bedhiafi Nov 06 '19 at 14:21
2 Answers
2
TRY FULL JOIN
SELECT no, namem, date FROM
TABLE1 FULL JOIN table2 ON 1 = 1
Try CROSS JOIN
SELECT no, namem, date FROM TABLE1 CROSS JOIN table2
Vignesh Kumar A
- 27,863
- 13
- 63
- 115
-
A join with an always true condition is a cross join. instead of `full join... on...` simply use `cross join`. – Zohar Peled Nov 06 '19 at 11:33
-
@VigneshKumarA I would not advise the usage of * in the select clause. It's unreliable. What if a password field is added to one of the tables with sensitive information? – Lajos Arpad Nov 06 '19 at 12:01
-
1
MS SQL Server 2017 Schema Setup:
create table Table1(num int,name varchar(max))
create table Table2(dates date)
insert into Table1(num,name)values(1,'aaa')
insert into Table1(num,name)values(2,'bbb')
insert into Table1(num,name)values(3,'ccc')
insert into Table2(dates)values('2019-01-01')
insert into Table2(dates)values('2019-02-02')
Query 1:
SELECT num,name,dates
FROM Table1
CROSS APPLY Table2
| num | name | dates |
|-----|------|------------|
| 1 | aaa | 2019-01-01 |
| 2 | bbb | 2019-01-01 |
| 3 | ccc | 2019-01-01 |
| 1 | aaa | 2019-02-02 |
| 2 | bbb | 2019-02-02 |
| 3 | ccc | 2019-02-02 |
Amira Bedhiafi
- 8,088
- 6
- 24
- 60