Also how do `LEFT JOIN`, `RIGHT JOIN` and `FULL JOIN` fit in?

What is the difference between &quot;INNER JOIN&quot; and &quot;OUTER JOIN&quot;?

Both these joins will give me the same results:

    SELECT * FROM table JOIN otherTable ON table.ID = otherTable.FK

vs

    SELECT * FROM table INNER JOIN otherTable ON table.ID = otherTable.FK

Is there any difference between the statements in performance or otherwise? 

Does it differ between different **SQL** implementations? 

Difference between JOIN and INNER JOIN

Syntax Error (missing Operator) in query expression &#39;tbl_employee.emp_id = tbl_netpay.emp_id INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID INNER JOIN tbl_tax ON tbl_employee.emp_id - tbl_tax.emp_ID&#39;.


    SELECT tbl_employee.emp_ID,
    tbl_employee.emp_name,
    tbl_gross.BasicSalary,
    tbl_gross.totalOT,
    tbl_netpay.totalGross,
    tbl_tax.totalLate,
    tbl_tax.allowance,
    tbl_tax.SSS,
    tbl_tax.PhilHealth,
    tbl_tax.GSIS,
    tbl_tax.HDMF,
    tbl_netpay.totalDeduc,
    tbl_netpay.emp_ti,
    tbl_netpay.emp_wt,
    tbl_netpay.emp_np
    FROM  tbl_employee
    INNER JOIN tbl_netpay ON tbl_employee.emp_id = tbl_netpay.emp_id
    INNER JOIN tbl_gross ON tbl_employee.emp_id = tbl_gross.emp_ID
    INNER JOIN tbl_tax ON tbl_employee.emp_id = tbl_tax.emp_ID;

I always get the error above.

Multiple INNER JOIN SQL ACCESS

For simplicity, assume all relevant fields are `NOT NULL`.

You can do:

    SELECT
        table1.this, table2.that, table2.somethingelse
    FROM
        table1, table2
    WHERE
        table1.foreignkey = table2.primarykey
        AND (some other conditions)

Or else:

    SELECT
        table1.this, table2.that, table2.somethingelse
    FROM
        table1 INNER JOIN table2
        ON table1.foreignkey = table2.primarykey
    WHERE
        (some other conditions)

Do these two work on the same way in `MySQL`?


INNER JOIN ON vs WHERE clause

I have a `users` table and a `payments` table, for each user, those of which have payments, may have multiple associated payments in the `payments` table. I would like to select all users who have payments, but only select their latest payment. I&#39;m trying this SQL but i&#39;ve never tried nested SQL statements before so I want to know what i&#39;m doing wrong. Appreciate the help

    SELECT u.* 
	FROM users AS u
		INNER JOIN (
			SELECT p.*
			FROM payments AS p
			ORDER BY date DESC
			LIMIT 1
        )
		ON p.user_id = u.id
	WHERE u.package = 1

MySQL INNER JOIN select only one row from second table

There are 2 tables, `spawnlist` and `npc`, and I need to delete data from `spawnlsit`.
`npc_templateid = n.idTemplate` is the only thing that &quot;connect&quot; the tables.
I have tried this script but it doesn&#39;t work.

I have tried this:

    DELETE s FROM spawnlist s
    INNER JOIN npc n ON s.npc_templateid = n.idTemplate
    WHERE (n.type = &quot;monster&quot;);



SQL DELETE with INNER JOIN

In trying to improve the speed of an immensely slow query (several _minutes_ on two tables with only ~50,000 rows each, on SQL Server 2008 if it matters), I narrowed down the problem to an `OR` in my inner join, as in:

    SELECT mt.ID, mt.ParentID, ot.MasterID
      FROM dbo.MainTable AS mt
      INNER JOIN dbo.OtherTable AS ot ON ot.ParentID = mt.ID
                                      OR ot.ID = mt.ParentID

I changed this to (what I hope is) an equivalent pair of left joins, shown here:

    SELECT mt.ID, mt.ParentID,
       CASE WHEN ot1.MasterID IS NOT NULL THEN
          ot1.MasterID ELSE
          ot2.MasterID END AS MasterID
      FROM dbo.MainTable AS mt
      LEFT JOIN dbo.OtherTable AS ot1 ON ot1.ParentID = mt.ID
      LEFT JOIN dbo.OtherTable AS ot2 ON ot2.ID = mt.ParentID
      WHERE ot1.MasterID IS NOT NULL OR ot2.MasterID IS NOT NULL

.. and the query now runs in about a second!

Is it generally a bad idea to put an `OR` in a join condition?  Or am I just unlucky somehow in the layout of my tables?

Is having an &#39;OR&#39; in an INNER JOIN condition a bad idea?

Is it possible to write join query without `ON` statement? and how do these joins differ `LEFT JOIN, RIGHT JOIN` works.

How to use mysql JOIN without ON condition?

What&#39;s the difference between `INNER JOIN`, `LEFT JOIN`, `RIGHT JOIN` and `FULL JOIN` 
in ***MySQL***?


What&#39;s the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

I have the below query which does not work. What am I doing wrong? Is this even possible?

    UPDATE ProductReviews AS R 
       INNER JOIN products AS P 
           ON R.pid = P.id 
    SET R.status = &#39;0&#39; 
    WHERE R.id = &#39;17190&#39; 
      AND P.shopkeeper = &#39;89137&#39;

SQL Server - inner join when updating

I&#39;m trying to inner join DataFrame A to DataFrame B and am running into an error.

Here&#39;s my join statement:

    merged = DataFrameA.join(DataFrameB, on=[&#39;Code&#39;,&#39;Date&#39;])

And here&#39;s the error:

    ValueError: len(left_on) must equal the number of levels in the index of &quot;right&quot;


I&#39;m not sure the column order matters (they aren&#39;t truly &quot;ordered&quot; are they?), but just in case, the DataFrames are organized like this:

    DataFrameA:  Code, Date, ColA, ColB, ColC, ..., ColG, ColH (shape: 80514, 8 - no index)
    DataFrameB:  Date, Code, Col1, Col2, Col3, ..., Col15, Col16 (shape: 859, 16 - no index)

Do I need to correct my join statement?  Or is there another, better way to get the intersection (or inner join) of these two DataFrames?


Why does Pandas inner join give ValueError: len(left_on) must equal the number of levels in the index of &quot;right&quot;?

I have this cross-database query...

    SELECT
                `DM_Server`.`Jobs`.*,
                `DM_Server`.servers.Description AS server,
                digital_inventory.params,
                products.products_id,
                products.products_pdfupload,
                customers.customers_firstname,
                customers.customers_lastname
            FROM `DM_Server`.`Jobs`
            INNER JOIN `DM_Server`.servers ON servers.ServerID = Jobs.Jobs_ServerID
            JOIN `cpod_live`.`digital_inventory` ON digital_inventory.jobname = Jobs.Jobs_Name
            JOIN `cpod_live`.`products` ON products.products_pdfupload = CONCAT(digital_inventory.jobname, &quot;.pdf&quot;)
            JOIN `cpod_live`.`customers` ON customers.customers_id = products.cID
            ORDER BY `DM_Server`.`Jobs`.Jobs_StartTime DESC LIMIT 50

it runs fine until I make them `LEFT JOIN`s.  I thought that by not specifying a type of join it was assumed to be a `LEFT JOIN`.  Is this not the case?

Difference in MySQL JOIN vs LEFT JOIN

I can currently query the join of two tables on the equality of a foreign/primary key in the following way.

  

     $result = mysql_query(&quot;SELECT * FROM `table1` 
                             INNER JOIN 
                           `table2` ON table1.primaryKey=table2.table1Id&quot;);

I&#39;d like to extend this to multiple tables (all with the same foreign keys). I am trying the following code which is not returning anything. Can anyone point out what I&#39;m doing wrong?

  

     $result = mysql_query(&quot;SELECT * FROM `table1` 
                            INNER JOIN `table2` 
                            INNER JOIN table3 
                            ON table1.primaryKey=table2.table1Id=table3.table1Id&quot;);



SQL Inner join more than two tables

I have this code

    User.find(:all, :limit =&gt; 10, :joins =&gt; :user_points,
                    :select =&gt; &quot;users.*, count(user_points.id)&quot;, :group =&gt;
                    &quot;user_points.user_id&quot;)

which generates following sql

    SELECT users.*, count(user_points.id) 
    FROM `users` 
    INNER JOIN `user_points` 
    ON user_points.user_id = users.id 
    GROUP BY user_points.user_id 
    LIMIT 10

is it possible to make LEFT JOIN instead of INNER JOIN other way than `User.find_by_sql` and manualy typing the query?

Rails ActiveRecord :joins with LEFT JOIN instead of INNER JOIN

I&#39;m trying to join 3 tables in a view; here is the situation:

I have a table that contains information of students who are applying to live on this College Campus. I have another table that lists the Hall Preferences (3 of them) for each Student. But each of these preferences are merely an ID Number, and the ID Number has a corresponding Hall Name in a third table (did not design this database...).

Pretty much, I have `INNER JOIN` on the table with their preferences, and their information, the result is something like...

     John Doe | 923423 | Incoming Student | 005

Where `005` would be the `HallID`. So Now I want to match that `HallID` to a third table, where this table contains a `HallID` and `HallName`.

So pretty much, I want my result to be like...

     John Doe | 923423 | Incoming Student | Foley Hall &lt;---(INSTEAD OF 005)

Here is what I currently have:

    SELECT
      s.StudentID, s.FName, 
      s.LName, s.Gender, s.BirthDate, s.Email, 
      r.HallPref1, r.HallPref2, r.HallPref3
    FROM
      dbo.StudentSignUp AS s 
      INNER JOIN RoomSignUp.dbo.Incoming_Applications_Current AS r 
        ON s.StudentID = r.StudentID 
      INNER JOIN HallData.dbo.Halls AS h 
        ON r.HallPref1 = h.HallID

SQL Inner-join with 3 tables?

I want to **delete** using `INNER JOIN` in **SQL Server 2008**.

But I get this error:

&gt; Msg 156, Level 15, State 1, Line 15    
&gt; **Incorrect** syntax near the **keyword** &#39;INNER&#39;.

My code:

    DELETE 
    FROM WorkRecord2 
    INNER JOIN Employee 
            ON EmployeeRun=EmployeeNo
    WHERE Company = &#39;1&#39; 
        AND Date = &#39;2013-05-06&#39;


How can I delete using INNER JOIN with SQL Server?

Is there any difference in terms of performance between these two versions of the same query?

    --Version 1
    SELECT p.Name, s.OrderQty
    FROM Product p
    INNER JOIN SalesOrderDetail s on p.ProductID = s.ProductID

    --Version 2
    SELECT p.Name, s.OrderQty
    FROM Product p
    INNER JOIN (SELECT ProductID, OrderQty FROM SalesOrderDetail) s on p.ProductID = s.ProductID

I&#39;ve heard it said (by a DBA) that Version 2 is faster because it fetches, within the inner SELECT statement, only the columns that are required for the query. But that doesn&#39;t seem to make sense, since query performance (as I know) is based on number of rows affected and final list of columns returned.

The query plans for both are identical, so I&#39;m guessing there isn&#39;t any difference between the two. 

Am I correct?

INNER JOIN vs INNER JOIN (SELECT . FROM)

I have a query which works fine in MySQL, but when I run it on Oracle I get the following error:

&gt; SQL Error: ORA-00933: SQL command not properly ended&lt;br/&gt;
&gt; 00933. 00000 -  &quot;SQL command not properly ended&quot;

The query is:

    UPDATE table1
    INNER JOIN table2 ON table1.value = table2.DESC
    SET table1.value = table2.CODE
    WHERE table1.UPDATETYPE=&#39;blah&#39;;

Update statement with inner join on Oracle

Need some SQL syntax help :-)

Both databases are on the same server

    db1 = DHE
    db2 = DHE_Import

    UPDATE DHE.dbo.tblAccounts 
    INNER JOIN DHE_Import.dbo.tblSalesRepsAccountsLink 
        ON DHE.dbo.tblAccounts.AccountCode = DHE_Import.tblSalesRepsAccountsLink.AccountCode 
    SET DHE.dbo.tblAccounts.ControllingSalesRep = DHE_Import.dbo.tblSalesRepsAccountsLink.SalesRepCode

I can do a query in Access with linked tables with similar syntax - BUT SQL doesn&#39;t like it.

I&#39;m sure it&#39;s a simple issue :-D

Thanks!

Update Query with INNER JOIN between tables in 2 different databases on 1 server

I&#39;ve got the basics of LINQ-to-SQL down, but I&#39;ve been struggling trying to get JOINs to work properly.  I&#39;d like to know how to convert the following to LINQ-to-SQL (ideally using method chaining, as that is my preferred format).

    SELECT      c.CompanyId, c.CompanyName,
                p.FirstName + &#39; &#39; + p.LastName as AccountCoordinator,
                p2.FirstName + &#39; &#39; + p2.LastName as AccountManager
    FROM        dbo.Companies c
    INNER JOIN  dbo.Persons p
    ON          c.AccountCoordinatorPersonId = p.PersonId
    INNER JOIN  dbo.Persons p2
    ON          c.AccountManagerPersonId = p2.PersonId


How do I convert multiple inner joins in SQL to LINQ?

Hey all i am trying to figure out how to go about inserting a new record using the following query:

    SELECT user.id, user.name, user.username, user.email, 
      IF(user.opted_in = 0, &#39;NO&#39;, &#39;YES&#39;) AS optedIn  
    FROM 
      user
      LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id
    ORDER BY user.id;

My `INSERT` query so far is this:

    INSERT INTO user 
    SELECT * 
    FROM user 
      LEFT JOIN user_permission AS userPerm ON user.id = userPerm.user_id;

However, i  am not sure how to do `VALUE(&#39;&#39;,&#39;&#39;,&#39;&#39;,&#39;&#39;, etc etc)` when using left and inner joins.

So what i am looking to do is this:

`User` table:

    id    | name       | username    | password                 | OptIn
    --------------------------------------------------------------------
    562     Bob Barker   bBarker       BBarker@priceisright.com   1

And also the `user_permission` table

    user_id   | Permission_id
    -------------------------
    562         4

**UPDATE**
So like this?

    INSERT INTO user (name, username, password, email, opted_in) VALUES (&#39;Bbarker&#39;,&#39;Bbarker&#39;,&#39;blahblahblah&#39;,&#39;Bbarker@priceisright.com&#39;,0);
    INSERT INTO user_permission (user_id, permission_id) VALUES (LAST_INSERT_ID(),4);

All Inner Join Solutions on Gang of Coders

Total of 21 Inner Join Solutions

What is the difference between "INNER JOIN" and "OUTER JOIN"?