Stack Overflow
Stack Overflow

Questions tagged [left-join]

A left join is an outer join which generates a result that contains all the records of the "left" table even when there are no matching records in other tables taking part in the join.

SQL LEFT JOIN ON returns the rows of INNER JOIN ON plus unmatched left table rows extended by NULLs. A consequence is that it returns all the rows from the left table at least once even if there are no matches in the left table. So if the ON clause matches 0 (zero) records in the left table then the join will still return that row extended by a NULL in each column from the right table.

Specific join tags

You can specify your question by adding extra tags:

Questions

8172 questions
2051
votes
13 answers

LEFT JOIN vs. LEFT OUTER JOIN in SQL Server

What is the difference between LEFT JOIN and LEFT OUTER JOIN?
KG Sosa
  • 21,565
  • 6
  • 26
  • 27
355
votes
10 answers

How do you perform a left outer join using linq extension methods

Assuming I have a left outer join as such: from f in Foo join b in Bar on f.Foo_Id equals b.Foo_Id into g from result in g.DefaultIfEmpty() select new { Foo = f, Bar = result } How would I express the same task using extension methods?…
LaserJesus
  • 8,230
  • 7
  • 47
  • 65
249
votes
4 answers

Select rows which are not present in other table

I've got two postgresql tables: table name column names ----------- ------------------------ login_log ip | etc. ip_location ip | location | hostname | etc. I want to get every IP address from login_log which doesn't have a row in…
stUrb
  • 6,612
  • 8
  • 43
  • 71
239
votes
7 answers

Left Join With Where Clause

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…
Sein Kraft
  • 8,417
  • 11
  • 37
  • 39
237
votes
8 answers

Are "from Table1 left join Table2" and "from Table2 right join Table1" interchangeable?

For example, there are two tables: 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…
Pankaj Agarwal
  • 11,191
  • 12
  • 43
  • 59
229
votes
6 answers

Deleting rows with MySQL LEFT JOIN

I have two tables, one for job deadlines, one for describe a job. Each job can take a status and some statuses means the jobs' deadlines must be deleted from the other table. I can easily SELECT the jobs/deadlines that meets my criteria with a LEFT…
fabrik
  • 14,094
  • 8
  • 55
  • 71
195
votes
9 answers

LEFT JOIN only first row

I read many threads about getting only the first row of a left join, but, for some reason, this does not work for me. Here is my structure (simplified of course) Feeds id | title | content ---------------------- 1 | Feed 1 | ... Artists artist_id…
KddC
  • 2,853
  • 2
  • 17
  • 19
161
votes
6 answers

Linq to Sql: Multiple left outer joins

I'm having some trouble figuring out how to use more than one left outer join using LINQ to SQL. I understand how to use one left outer join. I'm using VB.NET. Below is my SQL syntax. T-SQL SELECT o.OrderNumber, v.VendorName, …
Bryan Roth
  • 10,479
  • 15
  • 47
  • 56
152
votes
8 answers

LINQ to SQL: Multiple joins ON multiple Columns. Is this possible?

Given: A table named TABLE_1 with the following columns: ID ColumnA ColumnB ColumnC I have SQL query where TABLE_1 joins on itself twice based off of ColumnA, ColumnB, ColumnC. The query might look something like this: Select t1.ID, t2.ID, t3.ID …
aarona
  • 35,986
  • 41
  • 138
  • 186
129
votes
3 answers

How to use mysql JOIN without ON condition?

Is it possible to write join query without ON statement? and how do these joins differ LEFT JOIN, RIGHT JOIN works.
Alexander T.
  • 1,401
  • 2
  • 9
  • 11
128
votes
2 answers

SQL LEFT JOIN Subquery Alias

I'm running this SQL query: SELECT wp_woocommerce_order_items.order_id As No_Commande FROM wp_woocommerce_order_items LEFT JOIN ( SELECT meta_value As Prenom FROM wp_postmeta WHERE meta_key = '_shipping_first_name' …
CharleyXIV
  • 1,590
  • 3
  • 13
  • 24
124
votes
4 answers

Combine two pandas Data Frames (join on a common column)

I have 2 dataframes: restaurant_ids_dataframe Data columns (total 13 columns): business_id 4503 non-null values categories 4503 non-null values city 4503 non-null values full_address 4503 non-null values latitude …
anonuser0428
  • 11,789
  • 22
  • 63
  • 86
121
votes
2 answers

How to specify names of columns for x and y when joining in dplyr?

I have two data frames that I want to join using dplyr. One is a data frame containing first names. test_data <- data.frame(first_name = c("john", "bill", "madison", "abby", "zzz"), stringsAsFactors = FALSE) The other data…
Lincoln Mullen
  • 6,257
  • 4
  • 27
  • 30
106
votes
7 answers

Entity framework left join

How do I change this query so it returns all u.usergroups? from u in usergroups from p in u.UsergroupPrices select new UsergroupPricesList { UsergroupID = u.UsergroupID, UsergroupName = u.UsergroupName, Price = p.Price };
Lasse Edsvik
  • 9,070
  • 16
  • 73
  • 109
98
votes
4 answers

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

What is the difference in results between: RIGHT JOIN and RIGHT OUTER JOIN LEFT JOIN and LEFT OUTER JOIN ? Can you please explain it through some examples?
Puru
  • 8,913
  • 26
  • 70
  • 91
1
2 3
…
99 100