-2

What's the difference between this two SQL queries please?

SELECT 
    articles.idarticles, articles.titre, articles.contenu,
    categories.titre AS categorie 
FROM 
    articles, categories 
WHERE 
    idarticles = 2 ;

And this one 

SELECT 
    articles.idarticles, articles.titre, articles.contenu,
    categories.titre AS categorie 
FROM 
    articles 
LEFT JOIN 
    categories ON category_id = categories.idCategories 
WHERE 
    idarticles = 2 ;

The result is : for the first:

query one

The result for the second query is this :

query two

Why the first one returns one result, the second returns 2 rows?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Linux Guy
  • 1
  • 1
  • The second is using the standard, correct `JOIN` syntax. The first uses the original syntax introduced with SQL in the 1980, but which has been obsolete for decades. – Gordon Linoff Mar 16 '19 at 16:44
  • The first uses the **old, deprecated** syntax of just using comma-separated list of tables in the `FROM` clause - don't do this, it's been superseded for **more than 25 years** now by the **proper, ANSI/ISO** style of joins which the second query shows. – marc_s Mar 16 '19 at 16:53
  • 1
    Possible duplicate of [What's the difference between comma separated joins and join on syntax in MySQL?](https://stackoverflow.com/questions/20138355/whats-the-difference-between-comma-separated-joins-and-join-on-syntax-in-mysql) – Clay Mar 16 '19 at 20:51
  • This is an obvious & easily found faq. Before considering posting please always google many clear, concise & precise phrasings of your question/problem/goal and/or error message, with & without your particular strings/names, & read many answers. If you post a question, use one phrasing as title. PS Learn what left join on returns: inner join on rows plus unmatched left table rows extended by nulls. Always know what inner join you want as part of a left join. – philipxy Mar 16 '19 at 21:56
  • Please [use text, not images/links, for text--including tables & ERDs.](https://meta.stackoverflow.com/q/285551/3404097) Use images only for what cannot be expressed as text or to augment text. Images cannot be searched for or cut & pasted. Please in code questions give a [mcve]--cut & paste & runnable code plus desired output plus clear specification & explanation. Minimal means adding minimal problem code to minimal working code. So give minimal code that you show does what you expect & minimal code with the first place you go wrong. PS You don't even give input for your outputs. – philipxy Mar 16 '19 at 22:00

1 Answers1

0

Below query is returning single data because you have provided a particular id where it meets the criteria for fetching single row

SELECT articles.idarticles, articles.titre, articles.contenu,categories.titre AS categorie FROM articles, categories WHERE idarticles=2 ;

But in second query

SELECT articles.idarticles, articles.titre, articles.contenu,categories.titre AS categorie FROM articles LEFT JOIN categories ON category_id=categories.idCategories WHERE idarticles=2 ;

you have done left join with articles and categories where in there are 2 records for idarticles=2 in categories table which are matching the records of articles data. and since left join pulls all the records of first table and matching records of second table . you are getting two records.

for reference you can follow below link.

left join

Rajnish Pandey
  • 1
  • 2
  • Your "since" does not make sense since your description of left join is unhelpfully vague. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. PS The question is an obvious duplicate & should be downvoted & closed & not answered. – philipxy Mar 17 '19 at 07:34