# Nother question for SQL experts

**URL:** <https://boards.straightdope.com/t/nother-question-for-sql-experts/335537>\
**Category:** Factual Questions\
**Created:** [December 14, 2005, 4:19am UTC](https://boards.straightdope.com/t/nother-question-for-sql-experts/335537 "2005-12-14T04:19:48Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![Lobsang](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lobsang/32/4067_2.png) [@Lobsang](https://boards.straightdope.com/u/Lobsang)\
**Post date:** [December 14, 2005, 4:19am UTC](https://boards.straightdope.com/t/nother-question-for-sql-experts/335537/1 "2005-12-14T04:19:48Z")

</div>

Given a table where a field (named ‘field3’) contians account numbers what query would fill another table with records where the only matched field contains all the account numbers from the original table and blank data for the other records in the new table?

---

<div class="post-metadata">

**Author:** ![bitwise](https://avatars.discourse-cdn.com/v4/letter/b/ea5d25/32.png) [@bitwise](https://boards.straightdope.com/u/bitwise)\
**Post date:** [December 14, 2005, 4:32am UTC](https://boards.straightdope.com/t/nother-question-for-sql-experts/335537/2 "2005-12-14T04:32:01Z")

</div>

I think what you want is an outer join.

> **[Join (SQL)](https://en.wikipedia.org/wiki/Outer_join)**
>
> A join clause in SQL – corresponding to a join operation in relational algebra – combines columns from one or more tables into a new table. ANSI-standard SQL specifies five types of JOIN: INNER, LEFT OUTER, RIGHT OUTER, FULL OUTER and CROSS.
> To explain join types, the rest of this article uses the following tables:
> Department.DepartmentID is the primary key of the Department table, whereas Employee.DepartmentID is a foreign key.
> Note that in Employee, "Williams" has not yet been assigned to a ...

---

<div class="post-metadata">

**Author:** ![LSLGuy](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/lslguy/32/5813_2.png) [@LSLGuy](https://boards.straightdope.com/u/LSLGuy)\
**Post date:** [December 14, 2005, 1:49pm UTC](https://boards.straightdope.com/t/nother-question-for-sql-experts/335537/3 "2005-12-14T13:49:11Z")

</div>

Not sure I understand the question. Are you trying to copy the table but with all fields except the account number empty? Or are you trying to create a query which returns a result set as I described? Or are you trying to connect this account number table to some other existing table?

What dialect of SQL are you using? MS Access SQL, Oracle, MS real T-SQL, MySQL?

For the example’s sake, assume your source table has 4 fields, Field1 through Field4, and Field3 is your account number you want to preserve.

For the 2nd option, try something like

SELECT Field1=NULL, Field2=NULL, Field3, Field4=Null FROM TableName  
GO

If you want zeros or empty strings in the result instead of NULLs the query is almost same:

SELECT Field1=0, Field2=’’, Field3, Field4=’’ FROM SourceTableName  
GO  
If you were trying to copy the data to another existing table and want NULLS for the other fields you can use a simple SELECT with an INSERT like this:

INSERT INTO TargetTable SELECT Field3 FROM SourceTable  
GO

There are additonal options avaailable on the INSERT to control whther yuo get NULLs r DEFAULT values or … for the omitted fields.

I hope one ofthese willget you started in the right direction.