Re: SQL and IN

From: Alex Filonov <afilonov_at_yahoo.com>
Date: 1 Jul 2003 09:33:17 -0700
Message-ID: <336da121.0307010833.7d314f42@posting.google.com>

sal_phipps_at_hotmail.com (Sal) wrote in message news:<51925a3a.0306301510.32715549_at_posting.google.com>...
> "Christoph Seidel" <christoph.seidel1_at_gmx.de> wrote in message news:<bdp535$uv7b5$1_at_ID-143718.news.dfncis.de>...
> > Billy Verreynne wrote:
> > > Christoph Seidel wrote:
> > >
> > >>> To do this type of thing, I usually create a second table...
> > >>> You do an outer join between the date table and the widget table
> > >>
> > >> Well, but i do not have a second table to join with, that's the
> > >> point.
> > >
> > > IMO, that's the only way you can do it using regular SQL.
> >
> > Ok, thank you.
>
> Standard SQL will allow you to hard code tables too, e.g.
>
> with my_values(id) as (values 1, 2, 4)
> select a.id, b.* from my_values as a left join table as b on a.id = b.id;
>
> However I don't know whether Oracle supports this.
>

No, it doesn't. If list of values is not very big, you can use something like
select a.id, b*
from (select 1 from dual

      union all
      select 2 from dual
      union all
      select 4 from dual) a, t_b b

where a.id = b.id(+)

>
> Sal
Received on Tue Jul 01 2003 - 11:33:17 CDT