Is there a way to nesting one select statement to another one?

Question

select regexp_replace('home care','home care','care') as column1,
       regexp_replace('home care','home care','home') as column2
  from WORDLIST w

output:

column1 column2

 care    home

and then

select regexp_replace('care home','care home','home') as column3,
       regexp_replace('care home','care home','care') as column4
  from WORDLIST w

output:

column3 column4

  home     care

So for now I want to join these two regular expression together by nesting one select statement to another one without "join" words and also no where clause, the output should be as following:

column1 column2 column3 column4

  care   home  home     care

I would appreciate for any suggestion!

Why even select from `WORDLIST` table and not use it? At any rate, `select regexp_replace('home care','home care','care') as column1, regexp_replace('home care','home care','home') as column2, regexp_replace('care home','care home','home') as column3, regexp_replace('care home','care home','care') as column4 from WORDLIST w` will get what you want... I can't figure out why would want any of this though. Perhaps you are just giving us a terrible example though? — JNevill, Dec 04 '19 at 23:35
@JNevill Yes that's why I'm here for asking help, I'm learning regular expression. First I need to assign aliases to four columns and nesting one of them inside to another one(join operation) and also no where clause, no 'join' word. So I'm really confused right now. Is there a way to do that? — TIGUZI, Dec 04 '19 at 23:51
I mean... your question doesn't make much sense. Why are you selecting from `WORDLIST` table? You could select from `dual` and get the same results (or any table with a single record) so it's superfluous. You have two `SELECT` statements selecting from the same table, so there is no need for a join, just add the columns from one `SELECT` clause to the other `SELECT` clause as I did in my previous comment. No need to nest, join, or any other magic. — JNevill, Dec 04 '19 at 23:55

score 0 · Answer 1 · edited Dec 04 '19 at 23:45

0

The below code should work, it does use a forced join with 'join on 1 = 1'

select
w1.column1
,w1.column2
,w2.column3
,w2.column4
from(
select regexp_replace('home care','home care','care') as column1, regexp_replace('home care','home care','home') as column2 from WORDLIST w
) as w1
left join (
select regexp_replace('care home','care home','home') as column3, regexp_replace('care home','care home','care') as column4 from WORDLIST w
) as w2 on 1 = 1

edited Dec 04 '19 at 23:45

MT0

143,790
11
59
117

answered Dec 04 '19 at 23:44

Eddie Mayen

11
2

Thank you sir that's almost what I want, but is there a way to do that without word 'join'? – TIGUZI Dec 04 '19 at 23:54
I've never tried it, but this looks promising: https://stackoverflow.com/questions/25161747/how-to-implement-sql-joins-without-using-join – Eddie Mayen Dec 05 '19 at 00:08
What's exactly wrong with the word **join**, TIGUZI? – Littlefoot Dec 05 '19 at 06:31

score 0 · Answer 2 · answered Dec 05 '19 at 03:28

Well, there's a couple things you can try:

Just use a single SELECT:

select regexp_replace('home care','home care','care') as column1,
       regexp_replace('home care','home care','home') as column2,
       regexp_replace('care home','care home','home') as column3,
       regexp_replace('care home','care home','care') as column4
  from WORDLIST w

Use a CROSS JOIN:

SELECT s1.COLUMN1,
       s1.COLUMN2,
       s2.COLUMN3,
       s2.COLUMN4 
  FROM (select regexp_replace('home care','home care','care') as column1,
               regexp_replace('home care','home care','home') as column2
        from WORDLIST) s1
  CROSS JOIN (select regexp_replace('care home','care home','home') as column3,
                     regexp_replace('care home','care home','care') as column4
                from WORDLIST) s2

Note that these queries may return more rows than you want, depending on how many rows are in WORDLIST. db<>fiddle here

You might wish to consider SELECT...FROM DUAL

Is there a way to nesting one select statement to another one?

2 Answers2