sql - Left Outer Join using + sign in Oracle 11g -


can 1 tell me whether below 2 queries example of left outer join or right outer join??

table part: name         null?       type part_id      not null    varchar2(4) supplier_id              varchar2(4)  part_id supplier_id p1      s1 p2      s2 p3   p4    table supplier: name            null?     type supplier_id not null      varchar2(4) supplier_name   not null  varchar2(20)  supplier_id  supplier_name s1           supplier#1 s2           supplier#2 s3           supplier#3

display parts irrespective of whether supplier supplies them or not:

 select p.part_id, s.supplier_name part p, supplier s p.supplier_id = s.supplier_id (+)  select p.part_id, s.supplier_name part p, supplier s s.supplier_id (+) = p.supplier_id

thanks!

tablea left outer join tableb equivalent tableb right outer join table a.

in oracle, (+) denotes "optional" table in join. in first query, it's p left outer join s. in second query, it's s right outer join p. they're functionally equivalent.

in terminology, right or left specify side of join has record, , other side might null. in p left outer join s, p have record because it's on left, s null.

see this example java2s.com additional explanation.

to clarify, guess i'm saying terminology doesn't matter, it's there visualize. matters understand concept of how works.

right vs left

i've seen confusion matters in determining right vs left in implicit join syntax.

left outer join

select * a, b a.column = b.column(+)

right outer join

select * a, b b.column(+) = a.column

all did swap sides of terms in clause, they're still functionally equivalent. (see higher in answer more info that.) placement of (+) determines right or left. (specifically, if (+) on right, it's left join. if (+) on left, it's right join.)

types of join

the 2 styles of join implicit joins , explicit joins. different styles of writing joins, functionally equivalent.

see this question.

implicit joins list tables together. join conditions specified in clause.

implicit join

select * a, b a.column = b.column(+)

explicit joins associate join conditions specific table's inclusion instead of in clause.

explicit join

select * left outer join b on a.column = b.column

these implicit joins can more difficult read , comprehend, , have few limitations since join conditions mixed in other conditions. such, implicit joins recommended against in favor of explicit syntax.


Comments

Post a Comment

Popular posts from this blog

c++ - Is it possible to compile a VST on linux? -

for class project i'm attempting write vst plugin backed cuda. current cuda workflow on linux box, i'd prefer compile , link there. according wikipedia , should possible (i couldn't find steinberg documentation relevant linux) can't find makefile or instructions on how build if aren't using xcode or visual studio. i'm vst 3 sdk doesn't support linux. when try compile plugin under linux, error: ./base/source/fatomic.cpp:39:30: fatal error: libkern/osatomic.h: no such file or directory this issue caused following code in "vst3 sdk/base/source/fatomic.cpp" #if mac #include <libkern/osatomic.h> #if mac_os_x_version_min_required > mac_os_x_version_10_4 #define native_atomic_type (volatile int32_t*) #else #define native_atomic_type (int32_t*) #endif #elif windows #include <windows.h> #endif but hope compiling under linux work vst sdk 2.4. reading. jvstwrapper seems run on lin...
Read more

c# - SharpSVN - How to get the previous revision? -

i trying find efficient way previous revision of file text comparison using sharpsvn. using (svnclient c = new svnclient()) { c.authentication.defaultcredentials = new networkcredential( configurationmanager.appsettings.get("svnserviceusername") , configurationmanager.appsettings.get("svnservicepassword") , configurationmanager.appsettings.get("svnservicedomain") ); c.authentication.sslservertrusthandlers += new eventhandler<svnsslservertrusteventargs>(authentication_sslservertrusthandlers); collection<svnfileversioneventargs> fileversioncollection = new collection<svnfileversioneventargs>(); svnrevisionrange range = new svnrevisionrange(0, this.hooks.revision); svnfileversionsargs args = new svnfileversionsargs(); args.retrieveproperties = true; args.range = range; foreach (svnchangeitem item in log.changedpaths) { string path = this.repositorypath + it...
Read more

php cli reading files and how to fix it? -

while using php cli in ubuntu got apache web server cant read file can while using in browser. , error shows nothing , next used code testing purposes : <? $handle = fopen("testfile.txt", "r"); if ($handle) { while (($buffer = fgets($handle, 4096)) !== false) { echo $buffer; } if (!feof($handle)) { echo "error: unexpected fgets() fail\n"; } if(!is_readable($handle) { die("not readable"); } fclose($handle); } ?> how fix ? edit : after removing '@' before fopen got following error fopen(testfile.txt): failed open stream: no such file or directory in /var/www/log/ka.php on line 2 when run script through cli, uses shell's working directory, in opposition file's directory (which apache hands current directory). important you, since fopen call depends on relative path; , relative paths resolved relatively working directory, not script. to have script behave apache, eit...
Read more