SQL & PL/SQL :: Difference Between Equi And Inner Join

Aug 22, 2005

What are the main differences between an Equi Join and an Inner Join ?

View -1 Replies


ADVERTISEMENT

Forms :: Non-Equi Join In Oracle?

Sep 16, 2010

I want to create Non-Equi relation between EMP and Salgrade Table .I am writing following Query Under Relation Tab, Its giving me error message .

emp.sal between salgrade.losal and salgrade.hisal

View 3 Replies View Related

PL/SQL :: Difference - In Conditions (Join And Where Clause)

Sep 19, 2013

I need to be clear about what exactly difference when we put any condition in INNER JOIN and WHERE Clause. I tried both way and found same results. Even in Statistics Plan not much differences. 

1. Here I am using location filter in Inner join condition -

"SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl  On Gl.Location_Id = I.Location_Id   And Gl.Location_Id In ( 1767, 1747,202,1625)    Inner Join Sc1.Condition C On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0  And I.Condition_Id != 325         

2. Here I am using location filter in Where clause

SELECT I.*, Gl * From Sc1.Item I   Inner Join Sc1.Part P  On P.Part_Id = I.Part_Id       Inner Join Sc1.Location Gl   On Gl.Location_Id = I.Location_Id   Inner Join Sc1.Condition C        On C.Condtion_Id = Gl.Condition_Id Where  I.Inactive_Ind = 0       and I.LOCATION_ID in ( 1767, 1747,202,1625)    And I.Condition_Id != 325.

View 23 Replies View Related

SQL & PL/SQL :: Difference Between MINUS And LEFT Outer Join?

Jul 27, 2012

What is the fundamental difference between MINUS keyword and LEFT outer join in Oracle.

I can achieve same results using either one of them.

View 8 Replies View Related

PL/SQL :: Difference Between CROSS JOIN And Comma-notated Cartesian Product?

May 22, 2013

Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit
OS: Linux Fedora Core 17 (x86_64)

I was practicing on Recursive Subquery Factoring based on oracle examples available in the documentation URL....I was working on an example which prints the hierarchy of each manager with his/her related employees. Here is how I proceed.

WITH tmptab(empId, mgrId, lvl) AS
(
    SELECT  employee_id, manager_id, 0 lvl
    FROM employees
    WHERE manager_id IS NULL
    UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees, tmptab
    WHERE (manager_id = empId)
[code]....

107 rows selected.

SQL> However, by accident, I noticed that if instead of putting a comma between the table names I put CROSS JOIN, the very same query behaves differently.That is, if instead of writing

UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees, tmptab
    WHERE (manager_id = empId)I write
. . .
UNION ALL
    SELECT  employee_id, manager_id, lvl+1
    FROM employees CROSS JOIN tmptab
    WHERE (manager_id = empId)I get the following error message
ERROR at line 4: ORA-32044: cycle detected while executing recursive WITH query

I remember, oracle supports both comme notation and CROSS JOIN for Cartesian product (= cross product). For example

SQL> WITH tmptab1 AS
  2  (
  3      SELECT 'a1' AS colval FROM DUAL UNION ALL
  4      SELECT 'a2' AS colval FROM DUAL UNION ALL
  5      SELECT 'a3' AS colval FROM DUAL
  6  ),
 [code]....

SQL> So if both comma notated and CROSS JOIN have the same semantic, why I get a cycle for the above mentioned recursive subquery factoring whereas the very same query works pretty well with comma between the table names instead of CROSS JOIN? Because if a cycle is detected (ancestor = current element) this means that the product with CROSS JOIN notation is generating some duplicates which are absent in the result of the comma notated Cartesian product.

View 9 Replies View Related

SQL & PL/SQL :: Normal Join And Outer Join

Oct 19, 2013

Lets say I have three tables t1 and t2 and t3.

SELECT * FROM T1;

Id
____
1
2
3
4

SELECT * FROM T2;

Id
____
1

SELECT * FROM T3;

Id
____
1

Now when data exists in T2 and T3, I want to return only the records in T1 that match the records in T2 and T3 which is basically a normal join

select t1.id from t1, t2,t3 where t1.id = t2.id and t1.id = t3.id

However when there are no records in T2 or T3, I want to return all records in T1 i.e 1,2,3,4

One way of doing that is using the not exists clause

select * from t1 where not exists ( select null from t2 where t2.Id != t1.id) and not exists ( select null from t3 where t1.Id != t3.id)

Is there a better way of doing this in sql ?

View 5 Replies View Related

PL/SQL :: Calculate Difference By Which Day Count Of Difference Is Going?

Oct 17, 2012

Detail table will look like below:

Product_id issue_date action_date Force_date
1 10/10/2012 10/10/2012 10/10/2012
2 10/10/2012 10/10/2012 10/10/2012
3 10/10/2012 13/10/2012 15/10/2012
[code]....

Need the data like

Issue_date count_action_date count_Force_date (diff(action_date,force_date) 1 2 3 4 5 6(days since over)

10/10/2012 3 4 1 4 2 1 0 0

How to get the data like this? automatically how to get 123.... and how to calculate the difference by which day the count of difference is going?

View 3 Replies View Related

SQL & PL/SQL :: Left Outer Join Versus Right Outer Join

Aug 14, 2009

i want to know the difference between Left outer join Vs. Right outer join? Its like which join is safer to use or is there any recommendations to use any join?

View 6 Replies View Related

SQL & PL/SQL :: How To Join Two Where

Oct 15, 2012

I want to create a query with only the junction of these two queries:

- select id_utilizador,nbeneficiario,nome from adm_utilizadores a WHERE exists ( select 1 from adm_util_grupos b where
a.id_utilizador = b.id_utilizador and b.id_grupo = '1') ;

- SELECT b.*,rownum as row_num FROM ADM_UTILIZADORES b ORDER BY $sidx $sord
WHERE row_num BETWEEN $start AND $end";

Virtually want id_utilizador, nome, and nbeneficiario, I can get the first query, ordered me returning the first 10.

View 3 Replies View Related

Join Using First Match

Oct 2, 2011

I am new to sql..

I need to join 2 tables based on first match.. I cldnot use distinct on the result, as distinct work with entire row..

I cant use group by as well, since for group by hv to select all the columns which we need to display

View 1 Replies View Related

SQL & PL/SQL :: Join On Two Tables

Jul 29, 2013

I have 2 tables SEC_MASTER_HISTA and SEC_MASTER_HISTB.

Now, I need to compare the data of the two tables column-wise.

Ideally the 2 tables should have the same security_alias values but in my case they do not as the two tables belong to 2 diff client models. There is however a main SECURITY_MASTERA and SECURITY_MASTERB tables which have the security_alias recorded and a primary_asset_id column value which can act as a link between SEC_MASTER_HISTA and SEC_MASTER_HISTB. But, I have not been able to figure out the exact query which will be ideal.

Attached are the table structures and the data it contains.

Note: I need to compare the Coupon and Freq column values of SEC_MASTER_HISTA and SEC_MASTER_HISTB.

View 23 Replies View Related

SQL & PL/SQL :: Query On Self Join

Nov 13, 2011

The existing format of the records are

MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Cno
------ ---------- ---------- ---------- ---------- ---
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN 102
41958 1102 CHEN ZHEN Sub CHEN HONGJIAN 103
41960 2007 CHEN ZHEN Base CHEN ZHEN 104
41961 2007 CHEN ZHEN Sub CHEN HONGJIAN 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107

This should be modified as below.

MCF_ID MCF_PRDGRP MCF_BSCNME MCF_BSCSUP MCF_CRDNME Indicator Sub Name baseCno SubCno
------ ---------- ---------- ---------- ---------- ---------- -------- ------ ------
41956 1001 LIM KOK HWA Base LIM KOK HWA 101
41957 1102 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 102 103
41960 2007 CHEN ZHEN Base CHEN ZHEN Sub CHEN HONGJIAN 104 105
41968 2108 WEE LIANG Base WEE LIANG 106
41969 2108 LOW KAH Sub LOW KAH 107

[Code]..

Throught this query, I get a extra record with null value in the Sub_name and Sub_Cno.update this query using a self join.

View 5 Replies View Related

SQL & PL/SQL :: Using Update With A Join?

Mar 16, 2011

im trying to update a column in the employee table with the value "YES". Im getting an error message saying im missing a SET statement from this code below:

update e
SET e.review='YES'
from employee
inner join rentals r
on e.employee_id=r.employee_id
inner join job j
on e.job_id=j.job_id
where r.plate ='FY06WNT'
and j.function !='MANAGER'
and j.function !='PRESIDENT';

View 2 Replies View Related

SQL & PL/SQL :: How To Update With Join

Jul 7, 2010

drop table dev10 purge
/
drop table dev11 purge
/
drop table dev12 purge
/
create table dev10 as
select rownum c1, sys.dbms_random.string('U', 6) c2, trunc(sys.dbms_random.value(1, 7)) c3
from dual connect by rownum < 8
/

[Code]...

Now, Let us assume that, the record is

AAAAAA BBBBBB CCCCCC
DDDDDD EEEEEE FFFFFF
...
..
.

Now, we want dev12.c2 is 'FFFFFF' if dev11.c2 is 'BBBBBB', if we want to get this:

AAAAAA BBBBBB FFFFFF
DDDDDD EEEEEE FFFFFF
...
..
.

We can make this for SqlServer by coding:

UPDATE dev10
SET c3 = dev12.c1 FROM dev10 INNER JOIN dev11 ON dev11.c3 = dev10.c1 CROSS JOIN dev12
WHERE (dev11.c2 LIKE 'BBBBBB')
AND (dev12.c2 LIKE 'FFFFFF')
/
but, Oracle, what should we do new?

View 11 Replies View Related

Update With Join

Apr 13, 2011

This is my working query in ms access...

UPDATE Caxnode AS A INNER JOIN Caxnode AS B ON A.node_alias = B.node_alias SET A.partition_Type = 'LDOM', A.node_mode = 'LOGICAL', A.host_id = b.host_id, A.num_of_proc = b.num_of_proc WHERE (((A.node_mode)='virtual' Or (A.node_mode)='regular') AND ((B.partition_Type)='LDOM'));

This doesn't work in oracle, I googled and read that update doesnt work with inner join in oracle..

translate this query to work on oracle?

View 5 Replies View Related

SQL & PL/SQL :: Inner Join Used Using Clause?

Apr 11, 2012

Equi join (Inner join)

It is the simplest join or inner. An equijoin combines rows that have equivalent values for the specified columns.

SQL> select * from x;

NAME EMAIL EMPID
Sam email@removed 1060
Rose email@removed 1061

[code]....

don't consider above mentioned queries I got valuable outputs.

NAMEEMAIL EMPID NAME EMAIL EMPID
samemail@removed 1060 sam email@removed 1060
roseemail@removed 1061 rose email@removed 1061
sonaemail@removed 1062 sona email@removed 1062

Inner join shows matches only when they exist in both tables.so , i got records 1060,1061,1062

// Referencing columns used in a USING clause.
SQL> select x.name,x.email,x.empid from x
2 inner join y
3 using (empid);

select x.name,x.email,x.empid from x
*
ERROR at line 1:
ORA-25154: column part of USING clause cannot have qualifier

so query rewritten as

SQL> select x.name,x.email,empid from x
2 inner join y
3 using (empid);
NAME EMAIL EMPID
--------------- --------------- ----------
sam email@removed 1060
rose email@removed 1061
chris email@removed 1062

I mean see two different outputs.first output records twice displayed ... Yes i agree that is Inner join.second output records not displayed twice... common records only displayed once ,in x and y.

I think should n't use a table name or alias when referencing columns used in a USING clause... am i right ????

I want to know both are inner joins .how Oracle is determined both outputs ?

View 13 Replies View Related

SQL & PL/SQL :: What Is Hash Join

Jul 12, 2012

what is Hash join?how it is different from inner join?what is the sign used for inner join?(eg: like the (+) sign used for outer join)?

View 1 Replies View Related

PL/SQL :: How To Use Hash Join

Jan 13, 2013

If i have a table T1 and a table T2. Table T1 is having 100 rows and table T2 is having 20 rows. When performing a Hash join ,which table should be used to make the hash table ,the larger one or the smaller one and why ?IF the data set is too small for considerations then please conser table T1 with 10 million of rows and table T2 with 1 million of rows.

View 11 Replies View Related

Join Two Table In Different Instance

Nov 1, 2010

I have two table in different instance .

IMEI in instance A

RCA_SMART_CARD in instance B

Below is the desc table :

SQL> desc RCA_SMART_CARD;
Name Null? Type
----------------------------------------- -------- ----------------------------
N_CARD_ID NOT NULL NUMBER(10)
C_CARD_SERIAL_NUMBER NOT NULL VARCHAR2(20)
C_SIM_MSISDN VARCHAR2(20)
C_SIM_IMSI VARCHAR2(20)
C_LINKED_CARD VARCHAR2(20)
N_PRO_IDENTIFIER NOT NULL NUMBER(4)
C_CARD_TYPE VARCHAR2(1)
N_SIM_STATE NUMBER(1)
N_EEPROM_SPACE_LEFT NUMBER(9)
N_VOLATILE_SPACE_LEFT NUMBER(9)
N_NONVOLATILE_SPACE_LEFT NUMBER(9)
N_CARD_OPTI NOT NULL NUMBER(15)
N_PRODUCT_ID NUMBER(10)
D_CREATION_DATE DATE
D_MODIFICATION_DATE DATE
D_STATUS_MODIFICATION_DATE DATE

SQL> desc IMEI;
Name Null? Type
----------------------------------------- -------- ----------------------------
MSISDN NOT NULL VARCHAR2(20)
IMEI NOT NULL VARCHAR2(16)
DATE_MOD NUMBER(13)
IMSI VARCHAR2(18)
ICCID VARCHAR2(20)
T_PROF RAW(20)
EXTRA_DATA VARCHAR2(100)

If I want to join two table together . I want to search the number of record in IMEI that have N_SIM_STATE =1 in RCA_SMART_CARD . The MSISDN in IMEI is equal to C_SIM_MSISDN in RCA_SMART_CARD .

View 1 Replies View Related

Self Join No Longer Working?

May 20, 2010

I have a piece of code that joined the same table onto itself twice in order to get the previous, current and future year's into columns in the same output.

Up until recently this has been working fine but the most recent data has just been uploaded into the table and now it comes up with an error.

On the second (left outer) join it now says that the column is ambiguously defined (ORA-00918). It doesn't matter which order the joins are in it is always the second join that the error pops up on.

View 4 Replies View Related

Inner Join On Clause Statement?

Jul 21, 2011

I have 2 sql's statement below, and just wondering if their is difference between the two sql's.

FIELDS data type:
--------------------
a.field is DATE
b.field is also a DATE

SQL1:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field = b.field
WHERE a.field between b.field AND b.field + 2
;

SQL2:
-------
SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field between b.field AND b.field + 2
;

OR

SELECT a.*, b.*
FROM table a
INNER JOIN table b
ON a.field >= b.field AND
a.field <= (b.field + 2)
;

which ever is correct between the two sql.

QUESTION: would be the two sql's generate same result set.

View 1 Replies View Related

Natural Join With ON Clause?

Apr 22, 2012

CODESQL> select * from em1;

EMPID NAME SALARY
---------- ---------- ----------
1060 sam 4000
1061 rose 3700
1062 sona 4800

SQL> select * from dept;

EMPID NAME DEPT_NAME
---------- ---------- --------------
1060 sam INFO TECH
1061 rose BIO INFO
1063 chris COMP SCI
1064 maya MULTI MEDIA

I am TRYING to get output for on clause( NATURAL JOIN)

CODESQL> select x.empid,x.name,x.salary,y.dept_name from em1 x NATURAL JOIN dept y
2 on x.empid=y.empid;
on x.empid=y.empid
*
ERROR at line 2:
ORA-00933: SQL command not properly ended

[code]...

My questions are

** I think why NATURAL JOIN key word throws error.
** Second query succeed. i think it is inner join. am i right ??????
** If i execute query without alias why oracle throws error ???? Example shown below

I saw lot of examples like this
SQL> select empid,name,salary,dept_name from em1 natural join dept
2 on em1.empid=dept.empid;
on em1.empid=dept.empid
*
ERROR at line 2:

ORA-00933: SQL command not properly ended

View 1 Replies View Related

SQL & PL/SQL :: Multiple Join Table

May 17, 2010

oracle 10g to select column from about 8 table . I start with this statement

select A.a, B.b, C.c, D.d, E.e, F.f, G.g, H.h
from A
full outer join B on(A.a=B.b)
full outer join C on(B.b=C.c)
full outer join D on(C.c=D.d_
.
.
..
.
.
.

View 12 Replies View Related

SQL & PL/SQL :: Delete Statement With Inner Join?

Mar 14, 2011

I am trying to run following sql query,but it is throwing following error.

SQL> delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CON
NECTION_ID_TEMP=COMPLEMENTS.feature_conn_id
2 WHERE a.current_designation is null and a.current_low is null and a.current
_high is null;

delete from b$gc_count_temp a INNER JOIN COMPLEMENTS ON b$gc_count_temp.CONNECTI
ON_ID_TEMP=COMPLEMENTS.feature_conn_id
*
ERROR at line 1:
ORA-00933: SQL command not properly ended

View 4 Replies View Related

SQL & PL/SQL :: Writing A Join In Oracle 11g?

May 1, 2013

I have to do a query in oracle 11g

i want to cumpute the percentage of believers of every religion from the world's population

country:name,code,population

example data "Argentina" "ar" "39144753"

religion:country,name,percentage
example data
ar Jewish 2
ar Protestant 2
ar Roman Catholic 92

View 12 Replies View Related

SQL & PL/SQL :: Use Greater And Less Than In Left Join?

Apr 9, 2013

I am creating a query where I am trying to take phone call lengths and put them into buckets of length ranges 0:00 - 0:59, 1:00 - 1:59 etc. Even if there are no calls in the call table I need to return the range with a zero (hence the left join and nvl). When I do this the left join acts like an equal join, I suspect there is some reason left joins only work if there is an equal condition in the join (instead of >= and < that I use, or similarly I could use BETWEEN). I also have a question about performance (below).

The create table script for the lookup is like this:

CREATE TABLE DURATION_RANGES
(
RANGE_TEXT varchar2(20),
RANGE_LBOUND decimal(22),
RANGE_UBOUND decimal(22)
)

Sample inserts are:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0,59);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('01:00 - 01:59',60,119);
etc.

The query is:
select
r.range_text as duration_range,
nvl(count(*),0) as calls,
nvl(SUM(call_duration),0) as total_duration
from

[code]...

As I say, it is not returning all ranges in the duration_ranges table, so acting like an inner join. I realize one solution would be to populate duration ranges with every value possible (instead of ranges) so join is an equal join, but that would make the duration_range table larger.

My questions:
1. Is it possible to get the left join to work with the duration range values as they currently are?
2. Even if 1 is possible, would it be better performance to have exact values (but a larger lookup table) and do an equals join instead of >=, < or BETWEEN? Performance now is not bad.

What I mean is (with only one time value and not lbound and ubound:

INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',0);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',1);
INSERT INTO DURATION_RANGES (RANGE_TEXT,RANGE_LBOUND,RANGE_UBOUND) VALUES ('00:00 - 00:59',2);

View 4 Replies View Related

SQL & PL/SQL :: How To Join Two Columns Of Different Datatypes

May 3, 2012

I want to join two columns, one with a Long and another with a Varchar2.

How to achieve that?

View 9 Replies View Related

SQL & PL/SQL :: Join Two Queries Together To Get Result

Dec 12, 2011

How can i join two quires together to get result.

My requirement is:

First i want to select Table as we do

Select * from tab;

Then i want to describe the table as we do

Desc WO;

How can we join these two queries to have result.

View 21 Replies View Related

SQL & PL/SQL :: Query Without Outer Join

Nov 2, 2012

I have an attendance table in which we have empno, time_in, time_out..etc.

I want to create a matrix report that should shows all dates of any specific required month in columns and present empno in rows and time_in in cells.

I did it with using a temporary table by filling all the required dates and outer join with attendance table, it works fine, but it takes so long, is there any other better fast way to do it?

View 7 Replies View Related

SQL & PL/SQL :: Join Syntax In From Clause

Jul 21, 2010

I have this ORACLE SQL and just about understand join syntax in the From clause, i.e.

SELECT *
FROM TABLE 1 LEFT OUTER JOIN TABLE 2
ON TABLE1.FIELD_X = TABLE2.FIELD_X

However, I've inherited the sql below.

SELECT
RELOCATION.START_DATE,
STUDENT.SURNAME,
STUDENT.FORENAME
CURRENT_SCHOOL.BASE_ID
RELOCATIONS.STUD_ID
[code]......

I particularly don't understand this part

' FROM (MYDATABASE.STUDENT STUDENT
LEFT OUTER JOIN MYDATABASE.BASES CURRENT_SCHOOL '

why the table name student is referenced twice?And again for ' MYDATABASE.BASES CURRENT SCHOOL '?

When I put this into SSRS it shows only links between the tables STUDENT, RELCOATIONS and CURRENT_SCHOOL. Bases isn't mentioned in the tables diagram. it is still referred to in the raw SQL.

The above SQL works fine, i just don't understand what it's doing!

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved