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


ADVERTISEMENT

SQL & PL/SQL :: Select Statement From Schemas In MERGE Statement In USING Clause

Sep 13, 2013

In the following merge statement in the USINg clause...I am using a select stament of one schema WEDB.But that same select statement should take data from 30 schemeas and then check the condition below condition

ON(source.DNO = target.DNO
AND source.BNO=target.BNO);

I thought that using UNIONALL for select statement of the schemas as below.

SELECT
DNO,
BNO,
c2,
c3,
c4,
c5,
c6,
c7
[code]....

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

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 :: 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

SQL & PL/SQL :: Join Update Clause

Jun 9, 2010

I want to use join condition in update syntax.Like the following way but it doesnot work.how to fix it.

update tab_1 a
set a.qty = b.sell_qty
from tab_2 b
where a.nbr=b.nbr

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

Case Statement In Where Clause

Feb 1, 2012

Depending on which month the user is running this select the TAG_YEAR needs to be calculated differently. I have a feeling that I'm over thinking it.

SELECT DOG_MASTER.DOG_MASTER_ID,
DOG_NAME,
TAG_YEAR,
TAG_NUMBER AS PREVIOUSTAGNUMBER,
ISSUE_DATE
FROM DOG_OWNER
[code].......

View 1 Replies View Related

SQL & PL/SQL :: Case Statement In Where Clause

Aug 10, 2011

select empno,ename,deptno,employee_status from emp,dept where emp.deptno=dept.deptno and
( employee_status in(Case employee_status when {?Status}=1 then 'A'
when {?Status}= 2 then 'T'
When {?Status}= 3 then 'A'||','||'T'))
OR ( end_date >= {?START_DATE}
AND end_date <= {?END_DATE}
)
)

Since when i pass employee_status as input 1 it have given me 4 records. When I pass employee_status as input 2 it have given me 3 records. When I pass employee_status as input 3 it should give me 4 records + 3 records=7 records.

4 records for employee_status 'A'
3 RECORDS for employee_status 'T'
7 records for employee_status 'A' AND 'T'

How I should write a query to get 7 records.

View 2 Replies View Related

PL/SQL :: How To Use CASE Statement In Where Clause

May 16, 2013

How to use CASE stmt in WHERE clause?

View 3 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

PL/SQL :: Decode Statement Within Inner Join?

Apr 22, 2013

I'm having some trouble writing this query using a decode statement within a join. First, lets say I have two tables. Table A has the fields 'Baseline', 'NAD', 'Null', and 'Harn'. Table B has the fields 'Null', 'NAD', and 'Harn'. I want to join these tables together by these fields to return their ID's. Because table B doesn't have 'Baseline' to even have a match between the two tables, in Table A, we want to treat 'Baseline' as 'Harn', hence a decode statement. My query is below

Select
...
...
...
From
...
inner join oracle_srid_xref B on A.horizontal_datum = B.horizontal_datum and
decode(A.horizontal_epoch, 'BASELINE', 'HARN', loc.horizontal_epoch ) = B.horizontal_epoch

I'm not getting the ID's where the horizontal_epoch was initially 'Baseline'

View 5 Replies View Related

SQL & PL/SQL :: Syntax - Case Statement In Where Clause?

Mar 12, 2013

I am trying to use the following case statement in my where clause. My problem here is, I get no rows.

tab1.col1 =
case
when (tab1.col1 = 'VAR') and (tab1.col2 is null or tab1.col2 >= tab2.datecol) then
tab1.col1
else
null
end

View 13 Replies View Related

PL/SQL :: Merge Statement - Delete Clause?

Mar 14, 2013

I was reading about merge statement and tried some variations,

create table MERGE_TEST(
C1 number,
C2 varchar2(10 char),
c3 number);

insert into MERGE_TEST values(1, 'Name 3', 300);
insert into MERGE_TEST values(1, 'Name 2', 200);
insert into MERGE_TEST values(1, 'Name 1', 100);
commit;

[code]...

why is result different in this querys?

View 14 Replies View Related

PL/SQL :: Update Statement In Join Query

Nov 16, 2012

I've seen this example numerous places, and tried to implement it, but I keep getting an "invalid identifier" error message, despite the fact that I've got the table and column specifically identified.For instance, my query reads like:

UPDATE tbl1
SET tbl1.EMPID =
(SELECT  tbl2.EMPIDA  FROM tbl2 
WHERE LOWER(tbl1.EMAILCOL) =  LOWER(tbl2.EMAILCOL2)
)
WHERE tbl2.EMPIDA IN ('Z1O435','S8M4722','M0D5156')
AND EXISTS
(SELECT tbl2.EMPIDA
FROM tbl2
WHERE  tbl1.EMAILCOL= tbl2.EMAILCOL2 );

But I'll keep getting flagged at the tbl2.EMPIDA column reference. I have not tried this in SQL Plus, just in TOAD, but it seems to repeatedly fail.I have had to dump records to standalone Access tables and link back to perform the updates.

View 12 Replies View Related

SQL & PL/SQL :: How To Use (select Statement) Rather Than (in Clause) In Pivot Query

May 27, 2011

I'm using pivot query feature of oracle 11g and came across a strange situation where i need to pass a "select statement" in a "in clause" of pivot query.

SQL> CREATE TABLE TEST1
2 (
3 UIN NUMBER(8) NOT NULL,
4 TESTING_ID NUMBER(4),
5 PFA_RESULT VARCHAR2(30 BYTE)
6 );

[code]....

I have tried with pivot xml but it not giving desired output in sql*plus session.It is giving unreadable output.

select * from
(select uin,testing_id,pfa_result from test1)
pivot xml (max(pfa_result) as result
for (testing_id) in (select distinct testing_id from test1));

[code]....

Here actually i want to use "select distinct id from test1" instead of "in (11,12,13,14,15)". Because i don't know how many id's will be there and of which values. e.g. 11 or 21 or 25.

View 3 Replies View Related

SQL & PL/SQL :: Filter Data Using CASE Statement In WHERE Clause?

May 29, 2013

Want to filter a data using CASE statement in WHERE clause for the following scenario.

Need to Filter tb1.fallback_keyword if the fallback_flag is "Y' or 'N' and pg_number is null.Else no partial search of keyword.

where CASE WHEN (fallback_flg = 'Y' OR fallback_flg = 'N') and (pg_number is NULL )
THEN tb1.fallback_keyword = SUBSTR(key_word,1, INSTR(key_word,'#',-2))
ELSE (tb1.keyword = key_word ) AND (tb1.keyword like regexp_replace(key_word, '[*]+', '%'))
END

View 3 Replies View Related

SQL & PL/SQL :: View Not Having ORDER BY Clause In SELECT Statement?

Jun 13, 2010

currently i m going through some dumps for my OCA-11g prep.I came across one sentence :A view cannot have an ORDER BY clause in the SELECT statement.well this statement is false and the explanation given was :

Query operations containing ORDER BY clause are also permitted, so long as the ORDER BY clause appears outside the parentheses.

The following is an example of what I mean: CREATE VIEW my_view AS (SELECT*FROM emp) ORDER BYempno.

but when i tried running the query like this :CREATE VIEW my_view AS SELECT*FROM emp ORDER BYempno ,it worked w/o giving parentheses.

View 5 Replies View Related

PL/SQL :: Exclude Duplicate Values On SQL Where Clause Statement?

Jul 15, 2013

Are some posibilities to exclude duplicate values do not using sql aggregate functions in main select statement?  Priview SQL statement

SELECT * FROM
(
select id,hin_id,name,code,valid_date_from,valid_date_to
from diaries
)

[Code]....

 In this case i got duplicate of entry TT2 id 50513  In main select statement cant use agregate functions are even posible to exclude this value from result modifying only the QLRST WHERE clause (TRUNC need to be here)

View 5 Replies View Related

Making A Select Statement To Join A Few Tables Together?

May 2, 2009

I am trying to make a select statement to join a few tables together. What i would like to know is if i can do this by saying the following.

I want to select indviduals that have a skill, but i want to say that if they have any of these skills to show the name so for example

SELECT S.NAME
FROM EMPLOYEE S ,PROJECT_TEAM T, SKILL_LIST L
WHERE T.PROJECT = 'Tesco' AND
L.SKILLNO = 'skill1' or 'skill2' or 'skill3';

View 6 Replies View Related

SQL & PL/SQL :: Conditional Date Range Using CASE Statement In WHERE Clause

Mar 28, 2011

Is it possible within a CASE statement to put conditions on the date range that I want to pull? IE: am versus pm. The query has to pull specific time ranges for an AM run versus a PM run.
.....
FROM
table
WHERE
CASE
WHEN TO_CHAR(SYSDATE,'AM') = 'AM'
THEN table.date BETWEEN TRUNC(SYSDATE) AND SYSDATE
ELSE table.date BETWEEN TRUNC(SYSDATE+12/24) AND SYSDATE

View 6 Replies View Related

PL/SQL :: Save Exceptions Clause Or Equivalent For Insert As Select Statement

Jan 29, 2013

Is there a save exceptions clause or an equivalent for an Insert as select* statement ?

How do I trap the errors in the below statement -

INSERT INTO copy_emp
SELECT * FROM emp;

Is it an all or nothing scenario ?

View 10 Replies View Related

PL/SQL :: Passing String Values To Partition Clause In A Merge Statement?

May 24, 2013

I am using the below code to update specific sub-partition data using oracle merge statements.

I am getting the sub-partition name and passing this as a string to the sub-partition clause.

The Merge statement is failing stating that the specified sub-partition does not exist. But the sub-partition do exists for the table.

We are using Oracle 11gr2 database.

Below is the code which I am using to populate the data.

declare
ln_min_batchkey PLS_INTEGER;
ln_max_batchkey PLS_INTEGER;
lv_partition_name VARCHAR2 (32767);
lv_subpartition_name VARCHAR2 (32767);
begin

[code]....

View 2 Replies View Related

SQL & PL/SQL :: Update Statement With Join Only Update Matching Rows

Aug 17, 2010

This is my query:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c

[Code]...

The problem I am having is that it is updating all rows even when it is pulling back a null value for b.sak_request. I've tried adding b.sak_request is not null to the select statement like this:

UPDATE t_tt_hours a
SET a.sak_request = (
SELECT b.sak_request
FROM t_requests b, co c
WHERE b.nam_eds_tracking_id = c.id_dir_track_eds

[Code]...

but it doesn't seem to make a difference. The reason I need to do this is that the difference between where it matches with a valid (non-null) value is 396 rows vs. 12,484 rows which is too time consuming to run on my page.

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

SQL & PL/SQL :: OrderBy Clause Before From Clause?

Apr 23, 2010

can we use something like this

"select ... order by emp from emp"

what is to be done? so that this qurey runs. no co-related subquery to be used.

View 6 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

Variable Usage In Type Of Table Declaration Statement And Execute Immediate Statement

Aug 10, 2011

HOW to use variable P_TMPLID in following statement

TYPE typ_unrecon IS TABLE OF REC_' || P_TMPLID ||'_UNRECON%ROWTYPE index by binary_integer;

because its throwing error while compiling

and also in statement
FORALL i IN unrecondata.FIRST .. unrecondata.LAST SAVE
EXCEPTIONS
--STRSQL := '';
--STRSQL := ' INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES ' || unrecondata(i);
-- EXECUTE IMMEDIATE STRSQL;
INSERT INTO REC_' || P_TMPLID ||'_UNRECON VALUES unrecondata(i);---throwing error on this statement
commit;
--dbms_output.put_line(unrecondata(2).TRANSID);
EXCEPTION

View 2 Replies View Related

SQL & PL/SQL :: Select Statement Is Blocking A Delete Statement

Jan 11, 2012

I am using JDBC to run a few queries from my Java program (multi-threaded one).I am facing an issue where a select statement is blocking a delete statement. From the java code point of view, there are 2 different threads accessing the same tables (whith different DB connection objects).

When the block occurs (which i was able to find out from the java thread dump that there is a lock on oracle), the below is the output:

SQL> SELECT TO_CHAR(sysdate, 'DD-MON-YYYY HH24:MI:SS')
2 || ' User '||s1.username || '@' || s1.machine
3 || ' ( SID= ' || s1.sid || ' ) with the statement: ' || sqlt2.sql_text
||' is blocking the SQL statement on '|| s2.username || '@'
4 5 || s2.machine || ' ( SID=' || s2.sid || ' ) blocked SQL -> '
6 ||sqlt1.sql_text AS blocking_status FROM v$lock l1, v$session s1, v$lock l2 ,
7 v$session s2,v$sql sqlt1, v$sql sqlt2
8 WHERE s1.sid =l1.sid
9 AND s2.sid =l2.sid AND sqlt1.sql_id= s2.sql_id
AND sqlt2.sql_id= s1.prev_sql_id AND l1.BLOCK =1
10 AND l2.request > 0 AND l1.id1 = l2.id1 AND l2.id2 = l2.id2;
[code]...

From the above it can be seen that a select statement is blocking a delete. Unless the select is select for Update, it should not block other statements is not it ?

View 10 Replies View Related

SQL & PL/SQL :: Insert Into Statement Doesn't Insert All Rows Return By Select Statement?

Jan 12, 2011

If i inserted the values in table it gets inserting very few rows only.I dont know y it is?

View 15 Replies View Related







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