There is no difference between `RIGHT JOIN` and `RIGHT OUTER JOIN`. Both are the same. That means that `LEFT JOIN` and `LEFT OUTER JOIN` are the same. 

[Visual Representation of SQL Joins][2]


  [1]: http://i.stack.imgur.com/NGCFW.png
  [2]: http://www.codeproject.com/KB/database/Visual_SQL_Joins.aspx

Nice explanation of SQL Join:

![enter image description here][1]


Reference: [link][2]


  [1]: http://i.stack.imgur.com/JwLLy.png
  [2]: http://commons.wikimedia.org/wiki/File%3ASQL_Joins.svg

Here&#39;s a very nice [Visual Explanation][1] of joins generally by our very own Jeff Atwood.  A right outer join is the same as a right join, and left join and left outer join are also the same.

  [1]: http://www.codinghorror.com/blog/2007/10/a-visual-explanation-of-sql-joins.html

&gt; What is result difference between:
&gt; 
&gt; RIGHT JOIN and RIGHT OUTER JOIN

No difference.

&gt; LEFT JOIN and LEFT OUTER JOIN ?

No difference.

Simply put, the `OUTER` keyword is optional. You can include it or omit it without affecting the resultset.

I have a form where a user can add multiple select boxes for multiple cities. The problem is that each newly generated select box needs to have a unique id. Can this be done is JavaScript?

UPDATE: here is the part of the form for selecting cities. Also note that i&#39;m using some php to fill in the cities when a specific state is selected.

    &lt;form id=&quot;form&quot; name=&quot;form&quot; method=&quot;post&quot; action=&quot;citySelect.php&quot;&gt;
	&lt;select id=&quot;state&quot; name=&quot;state&quot; onchange=&quot;getCity()&quot;&gt;
		&lt;option&gt;&lt;/option&gt;
		&lt;option value=&quot;1&quot;&gt;cali&lt;/option&gt;
		&lt;option value=&quot;2&quot;&gt;arizona&lt;/option&gt;
		&lt;option value=&quot;3&quot;&gt;texas&lt;/option&gt;
	&lt;/select&gt;
	&lt;select id=&quot;city&quot; name=&quot;city&quot; style=&quot;width:100px&quot;&gt;
		
	&lt;/select&gt;

		&lt;br/&gt;
	&lt;/form&gt;

Here is the javascript:


	$(&quot;#bt&quot;).click(function() {

    $(&quot;#form&quot;).append(
           &quot;&lt;select id=&#39;state&#39; name=&#39;state&#39; onchange=&#39;getCity()&#39;&gt;
               &lt;option&gt;&lt;/option&gt;
               &lt;option value=&#39;1&#39;&gt;cali&lt;/option&gt;
               &lt;option value=&#39;2&#39;&gt;arizona&lt;/option&gt;
               &lt;option value=&#39;3&#39;&gt;texas&lt;/option&gt;
            &lt;/select&gt;
            &lt;select id=&#39;city&#39; name=&#39;city&#39; style=&#39;width:100px&#39;&gt;&lt;/select&gt;&lt;br/&gt;&quot;
         );
    });

create unique id with javascript

This is my jTable 

    private JTable getJTable() {
		String[] colName = { &quot;Name&quot;, &quot;Email&quot;, &quot;Contact No. 1&quot;, &quot;Contact No. 2&quot;,
				&quot;Group&quot;, &quot;&quot; };
		if (jTable == null) {
			jTable = new JTable() {
				public boolean isCellEditable(int nRow, int nCol) {
					return false;
				}
			};
		}
		DefaultTableModel contactTableModel = (DefaultTableModel) jTable
				.getModel();
		contactTableModel.setColumnIdentifiers(colName);
		jTable.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);
		return jTable;
	}

I will call this method to retrieve the data from database and put it into table model

    public void setUpTableData() {
		DefaultTableModel tableModel = (DefaultTableModel) jTable.getModel();
		ArrayList&lt;Contact&gt; list = new ArrayList&lt;Contact&gt;();
		if (!con.equals(&quot;&quot;))
			list = sql.getContactListsByGroup(con);
		else
			list = sql.getContactLists();
		for (int i = 0; i &lt; list.size(); i++) {
			String[] data = new String[7];
			
				data[0] = list.get(i).getName();
				data[1] = list.get(i).getEmail();
				data[2] = list.get(i).getPhone1();
				data[3] = list.get(i).getPhone2();
				data[4] = list.get(i).getGroup();
				data[5] = list.get(i).getId();
			
			tableModel.addRow(data);
		}
		jTable.setModel(tableModel);
	}

Currently I was using this method to refresh the table after updating the table data. I will first clear the table 

    DefaultTableModel tableModel = (DefaultTableModel) jTable.getModel();
    tableModel.setRowCount(0);

and then restructure the table model again so it will refresh the jTable. But I was thinking is there any best practices or better way to do that? 

JTable How to refresh table model after insert delete or update the data.

What is the difference in results between:
 
1. RIGHT JOIN and RIGHT OUTER JOIN 
2. LEFT JOIN and LEFT OUTER JOIN ?

Can you please explain it through some examples?


Difference between RIGHT &amp; LEFT JOIN vs RIGHT &amp; LEFT OUTER JOIN in SQL

<p>What is the difference in results between:</p>
<ol>
<li>RIGHT JOIN and RIGHT OUTER JOIN</li>
<li>LEFT JOIN and LEFT OUTER JOIN ?</li>
</ol>
<p>Can you please explain it through some examples?</p>


I came across an old table today with a datetime column called &#39;Created&#39; which allows nulls.  Now, I&#39;d want to change this so that it is NOT NULL, and also include a constraint to add in a default value (getdate()).

So far I&#39;ve got the following script, which works fine provided that i&#39;ve cleaned up all the nulls beforehand:

    ALTER TABLE dbo.MyTable ALTER COLUMN Created DATETIME NOT NULL 

Is there any way to also specify the default value as well on the ALTER statement?

Change a Nullable column to NOT NULL with Default Value

Is there any simple SQL editor in Eclipse? Or do I need to find a simple SQL editor plugin for Eclipse?

By simple I mean, the editor does **NOT** connect to any DB, does syntax highlighting and preferably formatting sql is a bonus.

Lightweight SQL editor for Eclipse

In my MySQL InnoDB database, I have dirty zip code data that I want to clean up.

The clean zip code data is when I have all 5 digits for a zip code (e.g. &quot;90210&quot;).

But for some reason, I noticed in my database that for zipcodes that start with a &quot;0&quot;, the 0 has been dropped.

So &quot;**Holtsville, New York**&quot; with zipcode &quot;`00544`&quot; is stored in my database as &quot;`544`&quot;

and

&quot;**Dedham, MA**&quot; with zipcode &quot;`02026`&quot; is stored in my database as &quot;`2026`&quot;.

What SQL can I run to front pad &quot;0&quot; to any zipcode that is not 5 digits in length? Meaning, if the zipcode is 3 digits in length, front pad &quot;00&quot;. If the zipcode is 4 digits in length, front pad just &quot;0&quot;.

**UPDATE**:

I just changed the zipcode to be datatype VARCHAR(5)

MySQL - how to front pad zip code with &quot;0&quot;?

I have a query like this:

    SELECT
        jobs.*, 
        (
            CASE
                WHEN lead_informations.state IS NOT NULL THEN lead_informations.state
                ELSE &#39;NEW&#39;
            END
        ) AS lead_state
    FROM
        jobs
        LEFT JOIN lead_informations ON
            lead_informations.job_id = jobs.id
            AND
            lead_informations.mechanic_id = 3
    WHERE
        lead_state = &#39;NEW&#39;

Which gives the following error:

    PGError: ERROR:  column &quot;lead_state&quot; does not exist
    LINE 1: ...s.id AND lead_informations.mechanic_id = 3 WHERE (lead_state...

In MySql this is valid, but apparently not in Postgresql. From what I can gather, the reason is that the `SELECT` part of the query is evaluated later than the `WHERE` part. Is there a common workaround for this problem?

Using an Alias column in the where clause in Postgresql

I have H2DB database which stores data in files. I have 3 files: `test.18.log.db`, `test.data.db`, and `test.index.db`.

I want get SQL dump file like when I use `mysqldump`. Is it possible?


How in H2DB get sql dump like in MySql?

How to perform left outer join in C# LINQ to objects without using `join-on-equals-into` clauses? Is there any way to do that with `where` clause?
Correct problem: 
For inner join is easy and I have a solution like this

    List&lt;JoinPair&gt; innerFinal = (from l in lefts from r in rights where l.Key == r.Key
                                 select new JoinPair { LeftId = l.Id, RightId = r.Id})

but for left outer join I need a solution. Mine is something like this but it&#39;s not working

    List&lt; JoinPair&gt; leftFinal = (from l in lefts from r in rights
                                 select new JoinPair { 
                                                LeftId = l.Id, 
                                                RightId = ((l.Key==r.Key) ? r.Id : 0
                                            })

where `JoinPair` is a class:

    public class JoinPair { long leftId; long rightId; }

LEFT OUTER JOIN in LINQ

How do you select all the columns from one table and just some columns from another table using JOIN? In MySQL.


MySQL Select all columns from one table and some from another table

I have a table customer that stores a customer_id, email and reference. There is an additional table customer_data that stores a historical record of the changes made to the customer, i.e. when there&#39;s a change made a new row is inserted.

In order to display the customer information in a table, the two tables need to be joined, however only the most recent row from customer_data should be joined to the customer table.

It gets a little more complicated in that the query is paginated, so has a limit and an offset.

How can I do this with MySQL? I think I&#39;m wanting to put a DISTINCT in there somewhere...

The query at the minute is like this-

    SELECT *, CONCAT(title,&#39; &#39;,forename,&#39; &#39;,surname) AS name
    FROM customer c
    INNER JOIN customer_data d on c.customer_id=d.customer_id
    WHERE name LIKE &#39;%Smith%&#39; LIMIT 10, 20

Additionaly, am I right in thinking I can use CONCAT with LIKE in this way?

(I appreciate that INNER JOIN might be the wrong type of JOIN to use. I actually have no clue what the difference is between the different JOINs. I&#39;m going to look into that now!)

MySQL JOIN the most recent row only?

I wish to know if I have a **join** query something like this - 

    Select E.Id,E.Name from Employee E join Dept D on E.DeptId=D.Id

and a **subquery** something like this -

    Select E.Id,E.Name from Employee Where DeptId in (Select Id from Dept)

When I consider **performance** which of the two queries would be faster and *why* ?

 Also is there a time when I should prefer one over the other?

Sorry if this is too trivial and asked before but I am confused about it. Also, it would be great if you guys can suggest me **tools** i should use to measure performance of two queries. Thanks a lot!


SQL Joins Vs SQL Subqueries (Performance)?

I am checking some old SQL Statements for the purpose of documenting them and probably enhancing them.

The DBMS is Oracle

I did not understand a statement which read like this:

    select ...
    from a,b
    where a.id=b.id(+)

I am confused about the `(+)` operator, and could not get it at any forums... (searching for + within quotes didn&#39;t work either).

Anyway, I used &#39;Explain Plan&#39; of SQLDeveloper and I got an output saying that `HASH JOIN, RIGHT OUTER`, etc.

Would there be any difference if I remove the `(+)` operator at the end of the query? Does the database have to satisfy some condition (like having some indexes, etc.) before `(+)` can be used?? It would be greatly helpful if you can provide me a simple understanding, or some good links where I can read about this.

Thanks!

Oracle &quot;(+)&quot; Operator

I have the following tables

1. ClassRoom     (ClassID,ClassName)
2. StudentClass  (StudentID,ClassID)
3. Student       (StudentID,StudentName,Etc..)
4. StudentDescription. (StudentDescriptionID,StudentID,StudentDescription)


I want to retrieve all the information on student==1

In sql I would do something like BELOW and get all the info about a student.

    
     select * from Student s
     join StudentClass sc on s.StudentID=sc.StudentID
     join ClassRoom c on sc.ClassID=c.ClassID
     left join StudentDescription sd on s.StudentID=sd.StudentID
     where s.StudentID=14

Now my problem.Using EF4 I did something like this but cannot make it work.
Also can you do an include and a left join

Attempt 1

    private static StudentDto LoadStudent(int studentId)
        {
            StudentDto studentDto = null;
            using (var ctx = new TrainingContext())
            {
                var query = ctx.Students
                    .Include(&quot;ClassRooms&quot;)
                    .Include(&quot;StudentDescriptions&quot;)
                    .Where(x=&gt;x.StudentID==studentId)
                    .SingleOrDefault();

                studentDto = new StudentDto();
                studentDto.StudentId = query.StudentID;
                studentDto.StudentName = query.StudentName;
                studentDto.StudentDescription = ??

            }

            return studentDto;
        }

Attempt 2 again incomplete and wrong

    using (var ctx = new TrainingContext())
             {
                 var query = (from s in ctx.Students
                                 .Include(&quot;ClassRooms&quot;)
                             join sd in ctx.StudentDescriptions on s.StudentID equals sd.StudentID into g
                             from stuDesc in g.DefaultIfEmpty()
                             select new
                                        {
                                            Name=s.StudentName,
                                            StudentId=s.StudentID,
                                            
             }).SingleOrDefault();



As you can see I dont know what I am doing here.
How can I convert that Sql into a EF Query?

Entity Framework include with left join is this possible?

I know about joins in SQL Server.

For example. There are two tables Table1, Table2. 

Their table structures are the following.

    create table Table1 (id int, Name varchar (10))
    
    create table Table2 (id int, Name varchar (10))


Table1 data as follows:

        Id     Name     
        -------------
        1      A        
        2      B    

Table2 data as follows:

        Id     Name     
        -------------
        1      A        
        2      B 
        3      C


If I execute both below mentioned SQL statements, both outputs will be the same


    select *
    from Table1
      left join Table2 on Table1.id = Table2.id
    
    select *
    from Table2
      right join Table1 on Table1.id = Table2.id

Please explain the difference between left and right join in the above SQL statements.

Difference between left join and right join in SQL Server

I need to retrieve all default settings from the settings table but also grab the character setting if exists for x character. 

But this query is only retrieving those settings where character is = 1, not the default settings if the user havent setted anyone.

    SELECT `settings`.*, `character_settings`.`value`
    FROM (`settings`)
    LEFT JOIN `character_settings` 
    ON `character_settings`.`setting_id` = `settings`.`id`
    WHERE `character_settings`.`character_id` = &#39;1&#39;  

So i should need something like this:

    array(
        &#39;0&#39; =&gt; array(&#39;somekey&#39; =&gt; &#39;keyname&#39;, &#39;value&#39; =&gt; &#39;thevalue&#39;),
        &#39;1&#39; =&gt; array(&#39;somekey2&#39; =&gt; &#39;keyname2&#39;),
        &#39;2&#39; =&gt; array(&#39;somekey3&#39; =&gt; &#39;keyname3&#39;)
    )
Where key 1 and 2 are the default values when key 0 contains the default value with the character value.

Left Join With Where Clause

I have two tables: `groups` and `group_members`.

The `groups` table contains all the information for each group, such as its ID, title, description, etc.  

In the `group_members` table, it lists all the members who are apart of each group like this:

    group_id | user_id
    1 | 100
    2 | 23
    2 | 100
    9 | 601

Basically, I want to list THREE groups on a page, and I only want to list groups which have MORE than four members.  Inside the `&lt;?php while ?&gt;` loop, I then want to four members who are apart of that group. I&#39;m having no trouble listing the groups, and listing the members in another internal loop, I just cannot refine the groups so that ONLY those with more than 4 members show.

Does anybody know how to do this? I&#39;m sure it&#39;s with MySQL joins.



MySQL joins and COUNT(*) from another table

Is it possible to use multiple left joins in sql query?

		LEFT JOIN
			ab 
		ON
			ab.sht = cd.sht

i want to add to attach one more query like this to it?
will it work?

		LEFT JOIN
			ab AND aa
		ON
			ab.sht = cd.sht
               AND
            aa.sht = cc.sht

Will this work?

How to use multiple LEFT JOINs in SQL?

I want to do a *[full outer join][1]* in MySQL. Is this possible? Is a *full outer join* supported by MySQL?

  [1]: https://en.wikipedia.org/wiki/Join_(SQL)#Full_outer_join




How can I do a FULL OUTER JOIN in MySQL?

I have a list of people&#39;s ID and their first name, and a list of people&#39;s ID and their surname. Some people don&#39;t have a first name and some don&#39;t have a surname; I&#39;d like to do a full outer join on the two lists.

So the following lists:

    ID  FirstName
    --  ---------
     1  John
     2  Sue

    ID  LastName
    --  --------
     1  Doe
     3  Smith

Should produce:

    ID  FirstName  LastName
    --  ---------  --------
     1  John       Doe
     2  Sue
     3             Smith

I&#39;m new to LINQ (so forgive me if I&#39;m being lame) and have found quite a few solutions for &#39;LINQ Outer Joins&#39; which all look quite similar, but really seem to be left outer joins.

My attempts so far go something like this:
     
    private void OuterJoinTest()
    {
        List&lt;FirstName&gt; firstNames = new List&lt;FirstName&gt;();
        firstNames.Add(new FirstName { ID = 1, Name = &quot;John&quot; });
        firstNames.Add(new FirstName { ID = 2, Name = &quot;Sue&quot; });
    
        List&lt;LastName&gt; lastNames = new List&lt;LastName&gt;();
        lastNames.Add(new LastName { ID = 1, Name = &quot;Doe&quot; });
        lastNames.Add(new LastName { ID = 3, Name = &quot;Smith&quot; });
    
        var outerJoin = from first in firstNames
            join last in lastNames
            on first.ID equals last.ID
            into temp
            from last in temp.DefaultIfEmpty()
            select new
            {
                id = first != null ? first.ID : last.ID,
                firstname = first != null ? first.Name : string.Empty,
                surname = last != null ? last.Name : string.Empty
            };
        }
    }
    
    public class FirstName
    {
        public int ID;
        
        public string Name;
    }
        
    public class LastName
    {
        public int ID;
        
        public string Name;
    }


But this returns:

    ID  FirstName  LastName
    --  ---------  --------
     1  John       Doe
     2  Sue

What am I doing wrong?

LINQ - Full Outer Join

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 two tables with similar column names and I need to return records from the left table which are not found in the right table? I have a primary key(column) which will help me to compare both tables. Which join is preferred?

How to return rows from left table not found in right table?

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

Content Type	Original Author	Original Content on Stackoverflow
Question	Puru	View Question on Stackoverflow
Solution 1 - Sql	Pranay Rana	View Answer on Stackoverflow
Solution 2 - Sql	Mahesh	View Answer on Stackoverflow
Solution 3 - Sql	Leonard	View Answer on Stackoverflow
Solution 4 - Sql	onedaywhen	View Answer on Stackoverflow

Difference between RIGHT & LEFT JOIN vs RIGHT & LEFT OUTER JOIN in SQL

Sql Problem Overview

Sql Solutions

Solution 1 - Sql

Solution 2 - Sql

Solution 3 - Sql

Solution 4 - Sql

JTable How to refresh table model after insert delete or update the data.

create unique id with javascript

Attributions