where vs inner join

Select name from city where CITY.COUNTRYCODE = COUNTRY.CODE;


Select NAME from CITY inner join CITY.COUNTRYCODE = COUNTRY.CODE;


in above queries what is difference between where and inner join

Are these queries are case sensitive like below


Select name from city where CITY.CountryCode = COUNTRY.Code;



Select name from city where CITY.COUNTRYCODE = COUNTRY.CODE;



below query how is different

SELECT City.Name
FROM City, Country
WHERE City.CountryCode = Country.Code AND Continent = 'Africa' ;

in the FROM clause do i need to mention both tables like city , country or one is ok then where


can i write above using inner clause without where?
any performance hits or advantages, disadvantage in either approaches?
LVL 7
gudii9Asked:
Who is Participating?
 
slightwv (䄆 Netminder) Commented:
>>Select NAME from CITY inner join CITY.COUNTRYCODE = COUNTRY.CODE;

This should generate an error.

>>Are these queries are case sensitive like below

In Oracle:  No.  I cannot speak to other database products.

>>below query how is different

It shouldn't be.




You can probably move MOST of your WHERE clause to the JOIN line itself.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
in above queries what is difference between where and inner join
Assuming that you forgot to add the COUNTRY table, there are no differences in terms of query execution. It's only a visualization difference as the INNER JOIN should be used to identify the relationship between the tables (but this is not really a requirement for query executions).

Are these queries are case sensitive like below
It depends on the collation of the databases. Databases that are configured to use case sensitive collations, needs to have the object names written in the correct case. Otherwise it doesn't matter.

in the FROM clause do i need to mention both tables like city , country or one is ok then where
FROM is only to specify table names and the type of JOINs.

can i write above using inner clause without where?
Yes.

any performance hits or advantages, disadvantage in either approaches?
No. As I mentioned before, it's only for visualization. I prefer to use the JOIN clause to identify the relationship between the tables but there's no impact of any kind.
0
 
pcelbaCommented:
SELECT City.Name
FROM City, Country
WHERE City.CountryCode = Country.Code AND Continent = 'Africa' ;

vs.

SELECT City.Name
FROM City
 INNER JOIN Country ON City.CountryCode = Country.Code
WHERE Continent = 'Africa' ;

Both ways are equivalent from the engine point of view.  JOIN is the recommended one.
You should qualify all columns and use schema in conjunction with table names so better is to use:

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code
WHERE Country.Continent = 'Africa' ;
0
Improve Your Query Performance Tuning

In this FREE six-day email course, you'll learn from Janis Griffin, Database Performance Evangelist. She'll teach 12 steps that you can use to optimize your queries as much as possible and see measurable results in your work. Get started today!

 
pcelbaCommented:
Case sensitivity depends on your Database settings. Used collation controls this behavior. More obvious is to ignore the difference between upper/lower case.
0
 
pcelbaCommented:
Of curse, you can also write:

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code AND Country.Continent = 'Africa' ;

with no difference comparing to the condition in WHERE (in this simple case).

This is not valid if you would use LEFT OUTER JOIN.
0
 
gudii9Author Commented:
Both ways are equivalent from the engine point of view.  JOIN is the recommended one.
why JOIN is recommended
This is not valid if you would use LEFT OUTER JOIN.
why not valid for left outer join.

is there something called left inner join? no right?
0
 
slightwv (䄆 Netminder) Commented:
When two tables are involved you ALWAYS JOIN.  The only difference is if you use the new ANSI join syntax or the old way of using both tables in the FROM and the columns in the where clause.

Most database these days allow the ANSI syntax and many find it easier to read so everyone is slowly starting to use it.

>>is there something called left inner join? no right?

Use the VENN diagram link I posted in your other question.  It will tell you the available options and what each one does.
0
 
pcelbaCommented:
To use JOINs instead of more tables in the WHERE clause is more descriptive and better understandable syntax. Tables in WHERE are always joined with INNER JOIN whereas JOIN allows more variants.

More about JOINS is described e.g. here: https://www.w3schools.com/sql/

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code
WHERE Country.Continent = 'Africa' ;

is exactly same as

SELECT City.Name
FROM dbo.City
 INNER JOIN dbo.Country ON City.CountryCode = Country.Code AND Country.Continent = 'Africa' ;

If you imagine a situation where some countries do not have continent assigned (Continent = NULL) then you would need to use OUTER JOIN:

SELECT City.Name, Country.Continent
FROM dbo.City
 LEFT OUTER JOIN dbo.Country ON City.CountryCode = Country.Code ;

Above select will list all cities with appropriate continent. If some country does not have continent assigned then you should see NULL in the Continent column in above select. You should also see NULL in cities which do not have Country assigned.

And now you may elaborate with additional filter:

SELECT City.Name
FROM dbo.City
 LEFT OUTER JOIN dbo.Country ON City.CountryCode = Country.Code
WHERE Country.Continent = 'Africa' ;

vs.

SELECT City.Name
FROM dbo.City
 LEFT OUTER JOIN dbo.Country ON City.CountryCode = Country.Code AND Country.Continent = 'Africa' ;

BTW, is your data model correct?  Some countries are on more than one continent. Examples are e.g. Russia, Turkey.
0
 
pcelbaCommented:
Hi gudii9,

did we explain everything about joins? Do you have additional questions about joins? If yes then please ask, if not then please award points and close the question.

TIA
Pavel
0
 
pcelbaCommented:
All answers were correct.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.