I have created 2 tables as  CREATE TABLE table1(customerName VARCHAR(20),custid NUMBER ,order_id NUMBER ,price NUMBER ); CREATE TABLE table2(id NUMBER ,salary NUMBER );   Now, I tried to use the queries   SELECT t1.customername,t1.custid,t2.salary FROM table1 t1 left join table2 t2 ON t1.custid = t2.id; SELECT t1.customername,t1.custid,t2.salary FROM table1 t1 left outer join table2 t2 ON t1.custid = t2.id;   But I get the same output. Is there any difference between them internally in their working ? or are both same!?
58553
What is the difference between LEFT JOIN and LEFT OUTER JOIN?
15299
Is there any efficiency difference in an explicit vs implicit inner join? For example:  SELECT * FROM table a INNER JOIN table b ON a.id = b.id;   vs.  SELECT a.*, b.* FROM table a, table b WHERE a.id = b.id;
111160
Practically, I would never store names for customers in a separate table.  But why not?  It's a one to many relationship (a person can have one name but that name can be used by multiple people).  Isn't it proper design to separate one to many relationships over multiple tables, or am I missing something?
28778
I'm having problems joining three tables. It works well when I use the same query for joining two tables (after removing LEFT OUTER JOIN C ON B.No = C.No). However, it doesn't work when I use three tables.  This is my query  SELECT A.Name, B.Quantity, C.Quantity  FROM A  LEFT OUTER JOIN B      ON A.ID = B.ID  LEFT OUTER JOIN C      ON B.No = C.No;
58842
Scenario:  Let's say I have two tables, TableA and TableB.  TableB's primary key is a single column (BId), and is a foreign key column in TableA.  In my situation, I want to remove all rows in TableA that are linked with specific rows in TableB: Can I do that through joins? Delete all rows that are pulled in from the joins?  DELETE FROM TableA  FROM    TableA a    INNER JOIN TableB b       ON b.BId = a.BId       AND [my filter condition]   Or am I forced to do this:  DELETE FROM TableA WHERE    BId IN (SELECT BId FROM TableB WHERE [my filter condition])   The reason I ask is it seems to me that the first option would be much more effecient when dealing with larger tables.  Thanks!
56409
I have found a query to find the nth highest salary from Employee table , but i don't understand the logic of (N-1)?  EmpID         Salary 1             90000 2             80000 3             54000 4             37000 5             12000 6             69000 7             50000  SELECT * FROM Employee E1 WHERE (N-1) = (                 SELECT COUNT(DISTINCT(E2.Salary))                 FROM Employee E2                 WHERE E2.Salary &gt; E1.Salary               )   If N= 4, then how does the query work? I'm a complete beginner in SQL, please help!
23741
states:  "In practice, explicit right outer joins are rarely used, since they can always be replaced with left outer joins and provide no additional functionality."  Can anyone provide a situation where they have preferred to use the RIGHT notation, and why? I can't think of a reason to ever use it. To me, it wouldn't ever make things more clear.  Edit: I'm an Oracle veteran making the New Year's Resolution to wean myself from the (+) syntax. I want to do it right
13495
I have 2 tables: Employees, attendanceIn   When I query using LEFT JOIN   SELECT employees.eno,employees.name,employees.dept,attendanceIn.attIn FROM `employees`  LEFT JOIN attendanceIn ON employees.eno = attendanceIn.eno WHERE date(attIn) like '2016-07-02%'   What I got is,     Whats wrong with my Query? How do I get my Expected result?
23931
My actual work query was an inner join, but this simple example with cross join seems to nearly always reproduce the problem.  SELECT * FROM (     SELECT 1 UNION ALL     SELECT 2 ) AA ( A ) CROSS JOIN (     SELECT NEWID() TEST_ID ) BB ( B )   With my inner join I had many rows for which I added to each a GUID using the NEWID() function, and for about 9 out of 10 such rows the multiplication with the 2-row virtual table produced the expected results, just 2 copies of the same GUID, while 1 out of 10 would produce different results. This was unexpected to say the least and gave me a really hard time trying to find this bug in my test data generation script.  If you take a look at the following queries using as well non-deterministic getdate and sysdatetime functions, you won't see this, I don't anyway-I always see the same datetime value in both final result rows.  SELECT * FROM (     SELECT 1 UNION ALL     SELECT 2 ) AA ( A ) CROSS JOIN (     SELECT GETDATE() TEST_ID ) BB ( B )  SELECT * FROM (     SELECT 1 UNION ALL     SELECT 2 ) AA ( A ) CROSS JOIN (     SELECT SYSDATETIME() TEST_ID ) BB ( B )   I'm currently using SQL Server 2008 and my work around for now is to load my rows with GUIDs into a table variable before finishing out my random data generation script. Once I have them as values in a table as opposed to virtual table, the problem goes away.  I have a workaround, but I'm looking for the ways to workaround without actual tables or table variables.  While writing this I tried without success these possibilities: 1) placing the newid() into a nested virtual table:  SELECT * FROM (     SELECT 1 UNION ALL     SELECT 2 ) AA ( A ) CROSS JOIN (     SELECT TEST_ID     FROM (         SELECT NEWID() TEST_ID     ) TT ) BB ( B )   2) wrapping the newid() within a cast expression such as:  SELECT CAST(NEWID() AS VARCHAR(100)) TEST_ID   3) reversing the order of appearance of the virtual tables within the join expression  SELECT * FROM (     SELECT NEWID() TEST_ID ) BB ( B ) CROSS JOIN (     SELECT 1 UNION ALL     SELECT 2 ) AA ( A )   4) using uncorrelated cross apply  SELECT * FROM (     SELECT NEWID() TEST_ID ) BB ( B ) CROSS APPLY (     SELECT 1 UNION ALL     SELECT 2 ) AA ( A )   Just before finally posting this question, now I tried this with success it seems, a correlated cross apply:  SELECT * FROM (     SELECT NEWID() TEST_ID ) BB ( B ) CROSS APPLY (     SELECT A     FROM (         SELECT 1 UNION ALL         SELECT 2     ) TT ( A )     WHERE BB.B IS NOT NULL ) AA ( A )   Anyone have any other more elegant, simpler workaround? I really don't want to use cross apply or correlation for a simple row multiplication if I don't have to.
49893
Found the following in an Oracle-based application that we're migrating (generalized):  SELECT     Table1.Category1,     Table1.Category2,     count(*) as Total,     count(Tab2.Stat) AS Stat FROM Table1, Table2 WHERE (Table1.PrimaryKey = Table2.ForeignKey(+)) GROUP BY Table1.Category1, Table1.Category2   What does (+) do in a WHERE clause? I've never seen it used like that before.
14064
My database contains three tables called Object_Table, Data_Table and Link_Table. The link table just contains two columns, the identity of an object record and an identity of a data record.  I want to copy the data from DATA_TABLE where it is linked to one given object identity and insert corresponding records into Data_Table and Link_Table for a different given object identity.  I can do this by selecting into a table variable and the looping through doing two inserts for each iteration.  Is this the best way to do it?  Edit : I want to avoid a loop for two reason, the first is that I'm lazy and a loop/temp table requires more code, more code means more places to make a mistake and the second reason is a concern about performance.  I can copy all the data in one insert but how do get the link table to link to the new data records where each record has a new id?
25283
This seems to be an area with quite a few myths and conflicting views.  So what is the difference between a table variable and a local temporary table in SQL Server?
18391
I'm trying to write a query that extracts and transforms data from a table and then insert those data into another table. Yes, this is a data warehousing query and I'm doing it in MS Access. So basically I want some query like this:  INSERT INTO Table2(LongIntColumn2, CurrencyColumn2) VALUES   (SELECT LongIntColumn1, Avg(CurrencyColumn) as CurrencyColumn1 FROM Table1 GROUP BY LongIntColumn1);   I tried but get a syntax error message.  What would you do if you want to do this?
113361
I'm using Oracle database and I've created a test table "Table1" with a varchar column "column1". I ran the following loop to add some rows:  BEGIN     FOR i in 1..10 LOOP         INSERT INTO TABLE1 (COLUMN1)          VALUES (i);     END LOOP; END;   If I run 'select count(*) from table1' I get '10' as my result. However, in SQL Developer, nothing shows up. I can add rows manually and commit them to the database(these appear in SQL Developer), but I can't seem to view the ones created in sqlplus command line. Why is that?
16869
I have some data tables that where imported in from a flat file source so everything is varchar(max) except FileRecordID which is a int that hold the row number the data came from in the flat file source (and is the primary key of the imported table.)  The following query works perfectly  SELECT *   FROM [CLIENT]   left outer join   (     select cast(ENTRY_CODE as int) as ENTRY_CODE, ENTRY_DESCRIPTION     from [ENTRY]     inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID     where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1   ) as discounts on CLIENT_CLASS = ENTRY_CODE   where Client_recd = 'A'   --and client.[FileRecordID] = 10607   I get the exact number of rows I was expecting and everything is fine. However if I uncomment that last line so I only show the record from the client data table who has  client.[FileRecordID] = 10607 it reports to me      Msg 245, Level 16, State 1, Line 1   Conversion failed when converting the varchar value 'BK/TN' to data type int.   The problem is from cast(ENTRY_CODE as int) as ENTRY_CODE in the first outer join. ENTRY can contain text in the ENTRY_CODE column, however all records that have ENTRY_TABLE set to the line number from TABLE where TABLE_CODE = 'CLTDISC' will always be numbers only. I perform the cast as ENTRY_CODE may or may not have leading 0's and spaces so I am trying to get them to be well formed.  What appears to me to be happening is adding client.[FileRecordID] = 10607 to the outer query causes the where clause not to be evaluated before the cast to int in the inner query.  I have tried things like adding and ISNUMERIC(ENTRY_CODE) = 1 to both the inner and outer queries but it has no affect.   How do I fix this? Can anyone explain what is happening so I can understand how to avoid this in the future?   Non filtered query planed execution   Filtered (non working) query planned execution  Notice the reversed order of the Compute Scalar and the Filter    Other things I have tried that do not work:   Move TABLE_CODE = 'CLTDISC' in to the on clause in the inner join. Added OPTION (FORCE ORDER) query hint to the query.     Update:  but it is very "hackish" IMHO, I really would still like a explanation of how to avoid things like this in the future and how to do this correctly.  SELECT *   FROM [CLIENT]   left outer join   (     select ENTRY_CODE, ENTRY_DESCRIPTION     from [ENTRY]     inner join [TABLE] on [ENTRY].ENTRY_TABLE = [Table].FileRecordID     where ENTRY_RECD = 'A' and TABLE_RECD = 'A' and TABLE_CODE = 'CLTDISC' and ISNUMERIC(ENTRY_CODE) = 1   ) as discounts on CLIENT_CLASS = REPLACE(LTRIM(REPLACE(ENTRY_CODE, '0', ' ')), ' ', '0')   where Client_recd = 'A'   and client.[FileRecordID] = 10607
45618
I was browsing through the questions and noticed this:  SELECT prodid, issue FROM Sales  WHERE custid = @custid  AND datesold = SELECT MAX(datesold)               FROM Sales s               WHERE s.prodid = Sales.prodid                   AND s.issue = Sales.issue                   AND s.custid = @custid   I was wondering what the "@" does in front of custID?  Is it just a way of referencing the custID from the table being selected?
20387
I have two tables A &amp; B. Table A id | name | num  Table B id | date | roll  id in table B is a foreign key which relates to the id in table A. I want to get the contents of table B where id is given and also along with that data, I need the name whose id is given from table A. NOTE: cannot use multiple queries separated by commas; need to do both the SELECTs in one query.