Compare inner join and outer join SQL statements
What is the difference between an inner join and outer join? What's the precise meaning of these two kinds of joins? ...
outer-join
Using outer joins in HQL queries
I'm trying to build a query using HQL and OUTER JOINs and just can't get it work. Consider the following mapping <class name="Parent"> <id name="id" type="integer"> <generator class="native" /> </id> </class> <class name="Child"> <id name="id" type="integer"> <generator class="native" /> </id> <many-to...
inserting rows from one table to another, which sql is more efficient (outer join vs sequential scan)
I need to copy over rows from Table B to Table A. The requirement is to only insert rows that are not already in A. My question is, which is of the the following two is more efficient: A) INSERT INTO A (x, y, z) SELECT x, y, z FROM B b WHERE b.id NOT IN (SELECT id FROM A); B) INSERT INTO A (x, y, z) SELECT b.x, b....
can this be written with an outer join
The requirement is to copy rows from Table B into Table A. Only rows with an id that doesn't already exist, need to be copied over: INSERT INTO A(id, x, y) SELECT id, x, y FROM B b WHERE b.id IS NOT IN (SELECT id FROM A WHERE x='t'); ^^^^^^^^^^^ Now, I was trying to write this with an outer join ...
Index usage with OUTER JOIN that contains IN statement
SELECT a.*, b.* FROM a LEFT OUTER JOIN b ON b.user IN (:userlist) AND b.key = a.fk_to_b WHERE a.user IN (:userlist) OR b.user IN (:userlist) Table b has an index of: (user, key) The database only uses the index when the :userlist parameter contains a single value. When :users contains multiple values (which ...
top 1 with a left join
Given the query below there might be multiple rows in dps_markers with the same marker key but we only want to join against the first. If I take this query and remove the top 1 and ORDER BY I get a value for mbg.marker_value but run as it is it always returns null SELECT u.id, mbg.marker_value FROM dps_user u LEFT JOIN (SELECT TO...
How to do a full outer join in Linq?
I've inherited a database that wasn't designed exactly optimally, and I need to manipulate some data. Let me give a more common analogy of the kind of thing I have to do: Let's say we have a Student table, a StudentClass table keeping record of all the classes he attended, and a StudentTeacher table that stores all the teachers who tau...
SQL: Get list of non-coupled items using MySQL 4
I'm not that good in SQL and I've come across a problem I don't know how to solve. I've read and re-read parts of a book about SQL (O'Reilly's Learning SQL) which I hoped would contain the information I needed but I haven't found it. My problem is the following. I'll use a simplified example to make it easier to discuss. I've got three...
(Lazy) LEFT OUTER JOIN using the Hibernate Criteria API
Hi all, I want to perform a LEFT OUTER JOIN between two tables using the Criteria API. All I could find in the Hibernate documentation is this method: Criteria criteria = this.crudService .initializeCriteria(Applicant.class) .setFetchMode("products", FetchMode.JOIN) .createAlias("products", "product"...
Oracle (Old?) Joins - A tool/script for conversion?
I have been porting oracle selects, and I have been running across a lot of queries like so: SELECT e.last_name, d.department_name FROM employees e, departments d WHERE e.department_id(+) = d.department_id; ...and: SELECT last_name, d.department_id FROM employees e, departments d WHERE e.department_i...
Linq to Entities and LEFT OUTER JOIN issue with MANY:1 relations
Can somebody tell me, why does Linq to Entities translate many to 1 relationships to left outer join instead of inner join? Because there's referential constraint on DB itself that ensures there's a record in the right table, so inner join should be used instead (and it would work much faster) If relation was many to 0..1 left outer joi...
Joining on a common table, how do you get a FULL OUTER JOIN to expand on another table?
I've scoured StackOverflow and Google for an answer to this problem. I'm trying to create a Microsot SQL Server 2008 view. Not a stored procedure. Not a function. Just a query (i.e. a view). I have three tables. The first table defines a common key, let's say "CompanyID". The other two tables have a sometimes-common field, let's say "...
Remove Duplicates from LEFT OUTER JOIN
Hey folk my question is quite similar to http://stackoverflow.com/questions/757957/restricting-a-left-join I have a variation in that request though and the comment didn't allow too many characters hence posting as a new question. I hope this doesn't go against the posting rules/etiquette. Assuming i have a table SHOP and another tabl...
How can I do a left outer join where both tables have a where clause?
Here's the scenario: I have 2 tables: CREATE TABLE dbo.API_User ( id int NOT NULL, name nvarchar(255) NOT NULL, authorization_key varchar(255) NOT NULL, is_active bit NOT NULL ) ON [PRIMARY] CREATE TABLE dbo.Single_Sign_On_User ( id int NOT NULL IDENTITY (1, 1), API_User_id int NOT NULL, extern...
Left Outer Join - SQL2005
I thought I knew enough SQL, but I am having problem with a left outer join. I have an expense detail record that needs to link to a table by dept and account_code. The query looks something like this: SELECT Detail.Spend, Budget.BudgetAmt FROM detail left outer join budget ON detail.dept = budget.dept AND dept.account_cod...
SQL : where vs. on in join
Perhaps a dumb question, but consider these 2 tables : T1 Store Year 01 2009 02 2009 03 2009 01 2010 02 2010 03 2010 T2 Store 02 Why is this INNER JOIN giving me the results I want (filtering the [year] in the ON clause) : select t1.* from t1 inner join t2 on t1.store = t2.store and t1.[year] = '2009'...
SQL: Gather right hand values from a join
Let's say a question has many tags, via a join table called taggings. I do a join thus: SELECT DISTINCT `questions`.id FROM `questions` LEFT OUTER JOIN `taggings` ON `taggings`.taggable_id = `questions`.id LEFT OUTER JOIN `tags` ON `tags`.id = `taggings`.tag_id I want to order the results according to a particular tag nam...
outer join for parent child chain
Considering below tables and relationships: parent --1:Many-- children --1:Many-- subchildren Parent may or many not have children records. children always have subchildren records. I want to write a query to select parent names where any if matched parent.name,children.name or subchildren.name. Here I understand I have to do a ...
sybase - values from one table that aren't on another, on opposite ends of a 3-table join
Hypothetical situation: I work for a custom sign-making company, and some of our clients have submitted more sign designs than they're currently using. I want to know what signs have never been used. 3 tables involved: table A - signs for a company sign_pk(unique) | company_pk | sign_description 1 --------------------1 ----------...
What are some good examples where SQL's OUTER JOIN is used?
I often get asked the questions in an interview that "what is an outer join in SQL"? While it can be answered, I wonder what might be some classic and good real life examples where a (LEFT) OUTER JOIN is used? ...